BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yashpande
Obsidian | Level 7
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
1 ACCEPTED SOLUTION

Accepted Solutions
yashpande
Obsidian | Level 7
Yes that's right.

View solution in original post

3 REPLIES 3
Reeza
Super User

Find the words after FROM/JOIN

 

yashpande
Obsidian | Level 7
Yes that's right.
PGStats
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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