topic Re: Ho to generate a list of tables and columns that are referenced by a SAS program in New SAS User

How to generate a list of tables and columns that are referenced by a SAS program

UL — Wed, 09 Jan 2019 17:59:21 GMT

Is there a way to generate a list of tables and columns that are referenced from SAS libraries? We've been generating these manually by scanning the SAS code but I am looking for a way how to do this programmatically.

Re: Ho to generate a list of tables and columns that are referenced by a SAS program

tomrvincent — Wed, 09 Jan 2019 14:11:00 GMT

Load your code into a dataset and search for keywords that indicate tables or columns. Parse the lines for individual values. That would be a start.

Re: Ho to generate a list of tables and columns that are referenced by a SAS program

SuryaKiran — Wed, 09 Jan 2019 14:12:57 GMT

Hello,

Welcome to SAS Community.

Are you trying to find the libraries and columns that your are using in your project or for the available ones? Querying the dictionary tables might be helpful (dictionary.tables and dictionary.columns)

Re: Ho to generate a list of tables and columns that are referenced by a SAS program

UL — Wed, 09 Jan 2019 15:53:17 GMT

Hi Suryakiran,

Thank you for your reply. I'm trying to find the columns that are used. Not the available ones. I wonder whether it is possible to generate a list like this:

SAS Program name ABC uses

column Database1.Schema1.Table1.Column1

and Database1.Schema1.Table1.Column2

and Database1.Schema1.Table1.Column5

and Database1.Schema1.Table5.Column1

and Database2.Schema1.Table1.Column1

etc.

Re: Ho to generate a list of tables and columns that are referenced by a SAS program

UL — Wed, 09 Jan 2019 15:55:59 GMT

Thanks Tom. Can this be done programmatically so that it populates a table, which has program name and table/column names used?

Re: Ho to generate a list of tables and columns that are referenced by a SAS program

Reeza — Wed, 09 Jan 2019 16:03:02 GMT

@UL wrote:

Thanks Tom. Can this be done programmatically so that it populates a table, which has program name and table/column names used?

You can use SCAPROC to some degree, but if a program is a macro for example, then you definitely cannot parse the code. Unfortunately work like this is only really accurate if done manually. You can do scans but since it's not 100% you need to do a lot of verification, which is more work IME.

Re: Ho to generate a list of tables and columns that are referenced by a SAS program

UL — Wed, 09 Jan 2019 16:44:55 GMT

Thank you Reeza,

I am now looking at using the logging facility as an alternative to scanning the code. Do you think this approach is promising?

Here is the documentation from SAS: https://documentation.sas.com/?docsetId=logug&docsetTarget=p1ux9lxccgetcgn1hmkhm64m3ud0.htm&docsetVersion=9.4&locale=en

Re: Ho to generate a list of tables and columns that are referenced by a SAS program

Reeza — Wed, 09 Jan 2019 17:06:20 GMT

Not likely. How are you planning to deal with variable and data set lists?

Or macro code that generates dynamic variable or data set lists?

EDIT: It really depends on how accurate you need this to be. If you're okay with a high level of error, say 25-30% then I think these would be fine. If you're doing a migration or transition plan/project, then no, this will not be sufficient.

You can't program your way out of bad documentation.

@UL wrote:

Thank you Reeza,

I am now looking at using the logging facility as an alternative to scanning the code. Do you think this approach is promising?

Here is the documentation from SAS: https://documentation.sas.com/?docsetId=logug&docsetTarget=p1ux9lxccgetcgn1hmkhm64m3ud0.htm&docsetVersion=9.4&locale=en

Re: Ho to generate a list of tables and columns that are referenced by a SAS program

ballardw — Wed, 09 Jan 2019 17:47:45 GMT

@UL wrote:

Hi Suryakiran,

Thank you for your reply. I'm trying to find the columns that are used. Not the available ones. I wonder whether it is possible to generate a list like this:

SAS Program name ABC uses

column Database1.Schema1.Table1.Column1

and Database1.Schema1.Table1.Column2

and Database1.Schema1.Table1.Column5

and Database1.Schema1.Table5.Column1

and Database2.Schema1.Table1.Column1

etc.

By database above are you referencing external database such as Oracle or Teradata? That would add some complexity depending on the database system and how you are referencing things.

Re: Ho to generate a list of tables and columns that are referenced by a SAS program

SuryaKiran — Wed, 09 Jan 2019 21:27:41 GMT

I don't think there is a straight forward approach for this, you may need to develop your own logic. Even though if you scan your .sas file, what if there is a select * program that selects all of the columns. Those columns names are not listed in the program but you are utilizing them.