topic Re: how to list the library and table used in a program in SAS Enterprise Guide

how to list the library and table used in a program

alepage — Thu, 22 Mar 2018 12:17:26 GMT

Hello,

On our server, we have many libraries as well as many tables into it. Some of those tables will be modify in the coming month.

As I am a new employee (5 months) and I am not familiar with all the sas programs and it became difficult to evaluate the impact of the table’s modifications on the existing programs.

I would like to know if there is a nice way to identify the libraries used in a program as well as the tables.

My goal is to set a list of the tables consulted in our programs by libraries. As we are informed about which tables will be modified, it will be easier to estimate the potential impacts of those tables’ updates.

Thanks for in advanced for your help

Regards,

Alain

Re: how to list the library and table used in a program

mnjtrana — Thu, 22 Mar 2018 13:43:09 GMT

If you are using SAS EG, i think there is no way to find out the libraries and the tables used int he program automatically, there is no in-built procedure.

However you can scan the whole code and find out which are the libraries and input or output.

other way could be to create a program which will read in your program as text file and create a dataset based on some keywords like - libname/filename/data/infile/merge etc.

In this way you could have some rows in the temp dataset which will show which libraries are there and corresponding tables, but it won't work sure shot for all input/outputs.

Filename inp "<path and name of the sas code/>";

data temp;
infile inp;
input rec $100.;
if index(lowcase(rec), "libname") ne 0 then output; /*check libraries */
else if index(lowcase(rec),"filename") ne 0 then output; /*check input files*/
else if index(lowcase(rec),"data") ne 0 and index(rec,".") ne 0 then output; /* to check the permanent datasets */
else if index(lowcase(rec),"merge") ne 0 and index(rec,".") ne o then output; /*check permanent datasets used in merge*/
run;

Re: how to list the library and table used in a program

Ksharp — Thu, 22 Mar 2018 13:53:51 GMT

proc sql;
select libname,memname
 from dictionary.members
  where memtype='DATA';
quit;

Re: how to list the library and table used in a program

Reeza — Thu, 22 Mar 2018 15:09:33 GMT

Look at PROC SCAPROC - unfortunately it requires you to run the program to see what it’s doing.

Re: how to list the library and table used in a program

mnjtrana — Fri, 23 Mar 2018 06:23:24 GMT

Hi Ksharp,

I think, we would have to run the program once to be able to use the dictionary tables.

Also that program should be run in the new session, without any autoexec or autoallocated libraries, otherwise it would give list of all the datasets, not necessarily used in the program.

Thanks,
Manjeet