topic How to find all table name in sas program ( prod sql) in SAS Programming

How to find all table name in sas program ( prod sql)

soujik — Thu, 04 Apr 2024 18:16:15 GMT

Hi, I have very log code using proc sql and retrieve data from lot of tables.

I am looking for any code which I can use to see only all the table name in code ( like i need list of the tables that are used in the program )

please help me

Re: How to find all table name in sas program ( prod sql)

soujik — Thu, 04 Apr 2024 18:38:48 GMT

all the program are in sas viya...please help to see the list of table name that are used in sas program

Re: How to find all table name in sas program ( prod sql)

Ksharp — Fri, 05 Apr 2024 03:00:49 GMT

Check PROC SCAPROC . Some code like the following:

proc scaproc;
record 'c:\temp\temp.txt';
run;

/****Your code here****/
data have;
 set sashelp.class;
run;
proc print data=sashelp.heart(obs=1);run;


proc scaproc;
write;
run;

And open "c:\temp\temp.txt"

Re: How to find all table name in sas program ( prod sql)

Patrick — Fri, 05 Apr 2024 04:15:17 GMT

To add to what @Ksharp wrote: Using Viya you can use global macro variable &_userhome to write the output to your home location.

proc scaproc;
  record "&_userhome/temp.txt";
run;

Re: How to find all table name in sas program ( prod sql)

soujik — Fri, 05 Apr 2024 20:03:38 GMT

Thank you but I have all my codes ( around 20 sas programs ) in this below sas directory. please help me how can i get all the table names from all sas programs which is in below directory

/cloud/pvxxx-123/mktprfl/890091254/daily_xref/code

Re: How to find all table name in sas program ( prod sql)

soujik — Fri, 05 Apr 2024 20:26:57 GMT

Thank you, but I have all sas codes ( around 20 sas programs) in below directory. i would like to get all table names from that all sas programs.

/cloud/pvxxx-123/mktprf/89009321/code/
sample sas program name
/cloud/pvxxx-123/mktprf/89009321/code/ext_xpo_map.sas

Re: How to find all table name in sas program ( prod sql)

soujik — Fri, 05 Apr 2024 21:14:07 GMT

all sas program running logs are writing into log directory

/cloud/pvxxx-123/mktprf/89009321/log

please help me to get all from table names for proc sql

Re: How to find all table name in sas program ( prod sql)

ballardw — Fri, 05 Apr 2024 21:15:25 GMT

If proc scaproc won't work for you there is likely to be no magic simple solution.

Parsing code text runs into complications quickly.

Consider, do you use macro variables to create any of the names? or macro expressions that result in a name?

Which procedures do you use? Some procedures can have many different input or output data sets depending on the procedure. For example Proc Survey select on the the Proc statement may have data sets referenced as:

Data = (pretty common)

Out = (also pretty common)

Outsort=

Certsize=

Maxsize=

Minsize=

Samprate= (or rate=)

Sampsize= (or N=)

Seed=

And while the above options can take a data set as input they also accept single or lists of values numeric values.

Does every single data step provide the name of an output data set? Does every single procedure have an input data set? SAS provides use of _last_, which need not appear in the code, where such aren't explicitly stated and trying to line up output that creates the specific _last_ may not be a trivial exercise.

If you don't know what I mean try running this very trivial code:

data ;
  x= 1;
run;

data ;
  y=2;
run;

Now look in your log. You may see something like:

112  data ;
113    x= 1;
114  run;

NOTE: The data set WORK.DATA1 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


115
116  data ;
117    y=2;
118  run;

NOTE: The data set WORK.DATA2 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time

Which on my system created data sets Work.Data1 and Work.Data2. But that is not possible to tell from parsing the code. And if someone uses similar code without explicit names you might have seen WORK.DATA33.

So, just how many options does your code use that might reference a data set? If we have to provide something that will find "every data set used" we have to write next to the equivalent of the SAS code to parse yours to find the sets.

Then there is the syntax used by ODS OUTPUT to create data sets.

We can likely look for simple DATA = , Set= , Data, Out= and maybe a few others. But generic code to find every single one in a robust reliable manner is sort of beyond simple help.

Re: How to find all table name in sas program ( prod sql)

Ksharp — Sat, 06 Apr 2024 04:47:23 GMT

You could make a new sas program file and put them all together. Like :

proc scaproc;
record 'c:\temp\temp.txt';
run;
%include '/cloud/pvxxx-123/mktprf/89009321/code/*.sas';
proc scaproc;
write;
run;

Re: How to find all table name in sas program ( prod sql)

Patrick — Sat, 06 Apr 2024 05:30:36 GMT

@soujik wrote:
Thank you, but I have all sas codes ( around 20 sas programs) in below directory. i would like to get all table names from that all sas programs.

/cloud/pvxxx-123/mktprf/89009321/code/
sample sas program name
/cloud/pvxxx-123/mktprf/89009321/code/ext_xpo_map.sas

@soujik

Best you can get is to actually execute the code and use Proc Scaproc to get some logging information that's then easy to parse for the information you're after.

Parsing actual SAS code to get this information is not only cumbersome but it will potentially also not return what you're after because there are many ways where table names get only determined during run time and they might differ between runs.

Consider for example code as below:

data demo;
  set mylib.monthly_tables_:;
run;

Which tables you actually pick will depend on what's stored under mylib at the time your code executes.