topic Re: Extract string from word in SAS Programming

Extract string from word

yashpande — Mon, 23 May 2016 00:36:19 GMT

Hi there,

I need to extract part of string. Actually my proc SQL code is saved in string and I need to find source table from it.

Below is my input

Code
Proc SQL; create table t1 as select a from b,c; 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; quit;

Output needed as below:
Source
B,c
T3,t4
T6,t8

Any help is really appreciated

Re: Extract string from word

Reeza — Mon, 23 May 2016 00:55:17 GMT

Find the words after FROM/JOIN

Re: Extract string from word

yashpande — Mon, 23 May 2016 02:38:01 GMT

Yes that's right.

Re: Extract string from word

PGStats — Mon, 23 May 2016 03:11:30 GMT

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;