Script to format SQL code

[Patrick]

Hi all, 

What I've got: 

A table with SQL scripts stored in a variable. Each script "Proc SQL;....QUIT;" stored in full in a single 32KB variable. 

What I'm looking for:
SAS code that formats the SQL on a level similar to tools like Poor Man's T-SQL Formatter. Basically, just adding some indention and LF to the existing scripts.

I've got some code that adds LF before SQL keywords but this doesn't sufficiently format the sometimes complex SQL's "pretty" enough so they become easy to read.

I'm working in a rather restrictive rhel environment and won't be able to install any 3rd party tools I could call out of SAS to do such formatting for me.

Thanks,

Patrick

[quickbluefish]

Sort of a fun problem, but probably a little crazy to try to do with SAS.  I'm not a giant advocate of the phrase, "don't reinvent the wheel", but I think it applies here.  There's just so much variety to what might happen in a SQL query in terms of LF and indentation that trying to capture it all would get crazy in a hurry.  My first suggestion would be (since you're in a Linux environment that almost certainly already has a Python interpreter (and likely Perl, etc.)) would be to go get the source code for an existing Python package, untar it on your own computer, get the .py file(s), and upload those as text files to your Linux environment (assuming that, at least, is allowed).  Then use that tool instead.  If you really want to do it in SAS, here's some syntax to get you started on a VERY basic SQL query - the output ends up in a file called "sql.txt".  

options nonotes nosource nosource2;
proc printto log="/path/to/folder/sql.txt"; run;

data _null_;
raw="create table test as select a.col1, b.col2 from test1 A inner join test2 B on a.col1=b.col1";
array lines {50} $250 _temporary_;
array spaces {50} 3 _temporary_;
retain linenum 0 spaces 1;
raw=compbl(upcase(raw));
first_as=index(raw," AS ");
linenum+1;
lines[linenum]=substr(raw,1,first_as+3);  * create table ... as ;
remaining=substr(raw,first_as+4);
first_from=index(remaining," FROM ");
linenum+1;
lines[linenum]=substr(remaining,1,first_from-1); * select ... ;
remaining=substr(remaining,first_from);
linenum+1;
lines[linenum]=substr(remaining,2,4); * from ;
remaining=substr(remaining,7);
first_join_start=prxmatch(
	"/\b((INNER|LEFT|RIGHT|FULL|CROSS|OUTER)\s)?JOIN\b/i",
	remaining); * not a complete list ;
linenum+1;
lines[linenum]=substr(remaining,1,first_join_start-1); * <tableA> A ;
spaces[linenum]=4;
remaining=substr(remaining,first_join_start);
first_join_end=index(remaining,"JOIN");
linenum+1;
lines[linenum]=substr(remaining,1,first_join_end+4);  * <join type> JOIN ;
spaces[linenum]=4;
remaining=substr(remaining,first_join_end+5);
first_on=index(remaining," ON ");
linenum+1;
lines[linenum]=substr(remaining,1,first_on-1);  * <tableB> B ;
spaces[linenum]=4;
remaining=substr(remaining,first_on);
linenum+1;
lines[linenum]=remaining;  * on a.var=b.var ;
spaces[linenum]=4;
put ' ';
do i=1 to linenum;
	t=spaces[i];
	ln=lines[i];
	put @t ln;
end;
run;

proc printto; run;

output looks like this:

[quickbluefish]

...you'd need to edit this since your input dataset has multiple queries - in particular, to reset the LINES and SPACES arrays to missing/defaults. Get rid of the RETAIN and instead just reinitialize them at the top of the DATA step.

[Patrick]

@quickbluefish  Thanks for your answer. And yes, I’m in the end creating an external .sql file per query.

What you’re doing is in essence similar to my current attempt. I’m using a RegEx with “all” the SQL keywords and using prxchange() simply add an LF before any keyword I find (changing the script directly within the one single var/row). After that I create a .sql file per row.

The SQL’s I’m dealing with can be rather complex and though the result using this simple approach can still be hard to read (like all the elements under a Select statement that can include rather lengthy expressions).

I guess what I’m asking for is if someone already spent the time writing some SQL formatter or knows about “published” existing code and is willing to share.

I’m working in a restrictive environment and won’t be allowed to install things like Python packages.

[quickbluefish]

Yeah, the only reason I mentioned the Python idea is that, at least for some packages, all you'd need to upload is plain text (.py though I usually change the extension to just .txt), not binaries / installers.  I work in a similar environment and can't install packages, but uploading plain text files, at least for me, is OK (since it's not really different than if I'd simply opened up an editor and typed in the Python script myself).  Good luck.

[Patrick]

@quickbluefish Well... Do you actually know a Python package that does such formatting? All I found so far are packages that require to run pip install

[quickbluefish]

You'd need to install on your own computer (using pip install) and then find the relevant .py files.  I tested this with the package called "sql-formatter".  This assumes you already have a python interpreter installed on your computer - if you happen to have a Mac or Linux OS, then that's already present.  Windows (which is what I use at home) may now have it by default as well.  

I just tried this and got it to work with just a few simple edits (there are 4 relevant .py files, one of which calls the others) -- meaning that if I take these 4 text files and upload them somewhere (changing the search path at the top as appropriate), I can simply pass a SQL query as either a string or file and it will spit back the formatted version.  If you're interested in doing the same, I'll describe the specific steps - will probably take you 10 min at most (once you have Python).

Here's the test I just did:

Here's how I called that script:

python test_sqlf.py

Here's the output (this was run in Powershell):

[Patrick]

Thanks @quickbluefish and it's certainly something I'll keep in mind for later and less restrictive environments. The one I'm working in is on 9.4 with noxcmd so can't really call python directly out of SAS even if all the necessary packages would be installed. 

[quickbluefish]

I see - yes, that would be a problem. I was guessing you were working in a terminal / Putty and running SAS from there.

[Quentin]

I like the Python idea. 

And <advertisement> you cold use PROC FCMP to call Python functions, so you can still run it from SAS.  If you're curious to see PROC FCMP -> Python in action, BASUG's free webinar on Wed April 23 will feature Troy M. Hughes, the man who wrote the book on PROC FCMP, showing this approach.  Register at: https://www.basug.org/events   </advertisement>

[RichardAD · Accepted solution]

A moderately simple SAS macro 

SAS Macros - by Richard A. DeVenezia - purtysql.sas

Realistically though, use any of the many SQL Pretty or SQL Formatter websites out there.

[Patrick]

@RichardAD 
Thank you and yes, this is what I was hoping for.