Create New Dialect for SAS PROC SQL

[LandonRGeorge]

Hello! Thank you for creating such a wonderful library...it really is a pleasure to use.

I understand my use case my be completely outside the scope of what this library is aiming for, but I am trying to parse out SAS PROC SQL. My goal is to parse out the metadata and not to transpile to another SQL dialect.

So far, I have done a bit of pre-processing to PROC SQL text to try and strip it of any SAS-specific syntax. However, I am starting to realize it may be more efficient/sane to extend the Dialect class to build up a custom dialect class that can handle the SAS-specific stuff.

If anyone is willing, I was hoping for some guidance in how I could parse out something like the following:

SELECT
    COALESCE(fieldA, fieldB) AS aliasAB,
    PUT(fieldC, best.) AS aliasC,
    PUT(INPUT(CALCULATED aliasAB, best20.), z20.) AS aliasE
from tbl

The PUT and INPUT functions are used to format fields. CALCULATED is a way to reference an operation that has previously occurred in the SQL expression.

[georgesittas]

Functions of the form name(arg1, ..., argn) are automatically handled by SQLGlot and they're either parsed into a "known" Func expression (i.e. AST node), for example exp.Avg, or into exp.Anonymous which is a "fallback" type for unknown functions.

Functions like PUT should thus be already handled for you (with the exception of best., which I'm not sure what it means and it's probably not handled at the moment). For functions like INPUT that have "special syntax", i.e. the CALCULATED part, you'll need to add an entry to FUNCTION_PARSERS to specify the specialized parsing logic. There are plenty of examples in the codebase, so you can use them as a guide.

You can also reach out in slack if you wanna chat about your use case in more depth.