DATA Step, Macro, Functions and more

Extract string from word

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Extract string from word

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

Accepted Solutions
Solution
‎06-05-2016 11:47 PM
Contributor
Posts: 44

Re: Extract string from word

Posted in reply to yashpande
Yes that's right.

View solution in original post


All Replies
Super User
Posts: 19,815

Re: Extract string from word

Posted in reply to yashpande

Find the words after FROM/JOIN

 

Solution
‎06-05-2016 11:47 PM
Contributor
Posts: 44

Re: Extract string from word

Posted in reply to yashpande
Yes that's right.
Respected Advisor
Posts: 4,925

Re: Extract string from word

Posted in reply to yashpande

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;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 288 views
  • 2 likes
  • 3 in conversation