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

[UL]

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.

[tomrvincent]

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.

[UL]

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

[Reeza]

---

@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.

[UL]

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&docsetVe...

[Reeza]

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&docsetVe...

---

[SuryaKiran]

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)

[UL]

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.

[ballardw]

---

@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.

[SuryaKiran]

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.