Scanning of SAS Code to find out input/output tables

[upadhi]

Hi team,

I have scanned a SAS code and able to able the proc sql statements in a table for example:

Line

proc sql;

create table x as select * from lib1.tab1 , lib2.tab2 where a=b;

create table x as select * from lib1.tab1 left join lib2.tab2 on a=b;

quit;

Next is i want to extract the library and table names from each of these line items. I understand we would need a recursive code as there can be multiple tables in from. Can anyone help me here

[ballardw]

You may need to answer some questions before anyone takes a stab at this.

First is the code ONLY, and I mean only, Proc SQL code?

Second, are all of the libraries that might be encountered defined in the session that will be searching for the data sets?

Third, do you need to identify WORK or USER library data sets?

The reason why these two questions are important because the main way that would be used to identify such are the lll.dddddd strings of library name and data set name. However that same construct is used in the data step with Hash objects, First. and Last. coding in data step, the Report Writing Interface coding and other places. If only Proc SQL code is involved then there is some chance.

With the advent of ODS OUTPUT statements you can't be sure that data sets always follow a very small number of key words like "create table" or "output=" or "out=". Macro coding can also seriously complicate identifying actual library and dataset names when they are build dynamically.

If other procedures and data step coding is involved then the only way to identify if a specific ABC. references a library named may be if the library is defined in the current session.

And last, if WORK or USER datasets need to be identified when single level names are used then the issue may get a bit more complicated because you have to assign the library when encountering possible data sets.

If the code is being run routinely you might investigate use of Proc SCAPROC which captures a lot of information about code as it runs and writes it into a text file. The text file would have searchable strings identifying output or input data sets.

[upadhi]

thanks for your reply. ideally i have to scan all procs and data steps.

i want something like this:

string: my aim is to find every word after BANK.upa for every bank.xx in this line bank.ff

output: upa xx ff

how can i extract the same