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:
... View more
