List tables code creates and calls from

[stacy_lee]

Hello,

I am looking to create a list of tables a program creates (create table) and uses (from or join). My goal is to drop tables when possible but I don't want to drop a table in Program 1 if it's used in Program 2 of the same project.

I have tried the following code to list work table but I also want to find permanent tables the program is creating and using.

proc sql ;
create table work_tables as
select * from dictionary.tables
where libname = 'WORK'
order by memname ;
quit ;

From this code, I'd like to see the tables it created and used such as;

work.Agreement_Data

work.Base_Data

work.Agreement_Prep

PROC SQL;
CREATE TABLE WORK.Agreement_Data as
Select
t1.*,
t2.*
FROM WORK.Base_Data t1
LEFT JOIN WORK.Agreement_Prep t2
ON t1.Local_Agreement_Identifier = t2.Local_Agreement_Identifier
AND t1.Audit_Request_Identifier = t2.Audit_Request_Identifier
/*AND MISSING(Detail.Audit_Request_Identifier)*/
;

you're time is appreciated.

[Reeza]

Use PROC SCAPROC and run your code and then check the log. It will have the information in there for you to parse more easily.
You can then parse out all the INPUT and OUTPUT records to easily filter out your tables required.

[jimbarbour · Accepted solution]

I think you have to examine the code itself.  Dictionary.Tables will tell you what tables exist after you've run your code, but I don't believe you can use Dictionary.Tables to predict what a given piece of SAS code will create.  I would parse the SAS code itself.  

First, I would create a little dataset containing all my Librefs, then I would TRANSPOSE the data so that each Libref is in its own column.  Like so:

DATA	My_Libnames;
	INFILE	DATALINES;
	INPUT	Libname	$;
	CALL	SYMPUTX('Column_Cnt', STRIP(PUT(_N_, 5.)), 'G');
DATALINES;
WORK
PROD01
DEV01
XREF
DB_01
DB_02
DB_03
;
RUN;

**------------------------------------------------------------------------------**;

PROC	TRANSPOSE	DATA=MY_Libnames
					OUT=My_Libnames_As_Cols(DROP=_NAME_);
	VAR LIBNAME;
RUN;

After creating a dataset containing my Libnames, I would read the SAS code I want to know about.  Here, I'm using DATALINES4 to bring the SAS code in, but if you have *.sas files, you would modify this code to use an INFILE.  Notice that the first three lines of the program are commented out.  The first three lines are an alternative method of reading in the data.  The first three lines work, but I can do the same thing with two lines if I use the $CHARw. Informat.  Not everyone is familiar with the $CHARw. Informat, so I thought it might be worth illustrating.

DATA	My_SAS_Code;
/*	LENGTH	Line_of_Code	$32767;*/
/*	INPUT;*/
/*	Line_of_Code	=		_INFILE_;*/
	INFILE	DATALINES4		TRUNCOVER;
	INPUT	Line_of_Code	$CHAR32767.;
DATALINES4;
PROC SQL;
CREATE TABLE WORK.Agreement_Data as
Select
t1.*,
t2.*
FROM WORK.Base_Data t1
LEFT JOIN WORK.Agreement_Prep t2
ON t1.Local_Agreement_Identifier = t2.Local_Agreement_Identifier
AND t1.Audit_Request_Identifier = t2.Audit_Request_Identifier
/*AND MISSING(Detail.Audit_Request_Identifier)*/
;
;;;;
RUN;

Then I would create a program similar to the below that would 1) create an array of my Libnames and then 2) search each line of code looking for a match on one of the Libnames in my array of Libnames.  When I get a match, I would parse that line of code for the table name.  In the below code, I have a line to remove the Libref if needed, but it is commented out.  I think it's better to have the Libref included with the table name because you might have a both a WORK.my_very_important_data as well as a PERM.my_very_important_data.  As coded, this program would identify both.  If you don't want the Libname as part of the table name, uncomment that one line of code.  This program could be combined with the previous program that reads the SAS code but is here separated so that it's a little easier to see what I'm doing.

DATA	My_Tables(KEEP=Table_Name);
	RETAIN	First_Time	1;
	LENGTH	Table_Name	$128.;

	IF	First_Time				THEN
		DO;
			First_Time		=	0;
			SET	WORK.My_Libnames_As_Cols;
		END;

	ARRAY	My_Libnames	{*}	$8	Col1-Col&Column_Cnt;

	SET	My_SAS_Code;

	DO	i					=	1	TO	&Column_Cnt;
		Libref_Position		=	INDEX(Line_of_Code, STRIP(My_Libnames{i}));
		IF	Libref_Position		THEN
			DO;
				Table_Name	=	SCAN(SUBSTR(Line_of_Code, Libref_Position),1,' ;*');
*				Table_Name	=	TRANWRD(Table_Name, CATS(My_Libnames[i}, '.'), '');
				OUTPUT;
			END;
	END;

	DELETE;
RUN;

 Jim

[stacy_lee]

Reeza, thank you so much. I will have to learn more about Proc SCAPROC as I wasn't able to figure it out.

Jim, thank you as well. I will also have to study how and why this works, but it did.

I appreciate the time and information both of you provided!