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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.