Find the words after FROM/JOIN
This will work for simple queries (e.g. no subqueries)
data queries;
length query $200;
infile datalines truncover;
input query $char100.;
datalines4;
Proc SQL; create table t1 as select a from b, c,d; quit;
Proc SQL; create table t2 as select a from t3 inner join t4 on t3.id=t4.Id ; quit;
Proc SQL; create table t5 as select a from t6 left join t7 on t6.id=t8.id left join t8 on t6.id=t8.id; quit;
;;;;
data want;
length tbl $64 tbls $200;
if not prx1 then prx1 + prxparse("/\bfrom\s+(\w+)((?:\s*,\s*(?:\w+))*)\b|\bjoin\s+(\w+)\b/i");
set queries;
start=1; stop = -1;
call prxnext(prx1, start, stop, query, pos, len);
do while(pos > 0);
tbl = catx(",",
cats( prxposn(prx1, 1, query),
compress(prxposn(prx1, 2, query))),
prxposn(prx1, 3, query));
tbls = catx(",", tbls, tbl);
call prxnext(prx1, start, stop, query, pos, len);
end;
drop prx1 tbl start stop pos len;
run;
proc print data=want; var tbls; run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.