Help using Base SAS procedures

PROC CONTENTS of entire library with all variables detail

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

PROC CONTENTS of entire library with all variables detail

I am trying to output my results of PROC CONTENTS for an entire library, to a SAS dataset. I want the details of each variable and the dataset it is located in. Then export that dataset to Excel spreadsheet.
I am using SAS 9.1.3 on WindowsXP Professional platform.

I can't reach the person that did this before, but he got PROC CONTENTS to
print variable name in first column followed by table name in second column, then attributes and other details in subsequent columns. This allowed anyone to search for a variable and immediately find the table it is located in.

Example of what I want the layout to look like:

VARIABLE TABLE NAME

borrower_name Customer
address Customer
account_num Loan_Details
loan_amount Loan_Details
interest_rate Loan_Details

Accepted Solutions
Solution
‎01-24-2017 09:24 AM
Super User
Posts: 3,112

Re: PROC CONTENTS of entire library with all variables detail

[ Edited ]

Another way is this:

/* Note that value of libname is UPPERCASE */
proc sql;
create table columns as
select name as variable
,memname as table_name
from dictionary.columns
where libname = 'WORK'
;
quit;

/* SAS 9.4 or later */
ods excel file="c:\temp\variables.xlsx" style=minimal;
proc print data=columns;
run;
ods excel close;

/* earlier versions, using SAS/ACCESS to PC Files */
PROC EXPORT data = columns
OUTFILE = 'variables.xls' 
DBMS = EXCEL REPLACE;
SHEET='VARLIST'; 
RUN;

View solution in original post


All Replies
Contributor
Posts: 61

Re: PROC CONTENTS of entire library with all variables detail

Sorry, my first post was not clear. I had to put "|" separaters in this message text to show column separation. It wouldn't let me display spaces in tabular format.

VARIABLE | TABLE_NAME I

borrower_name | Customer |
address | Customer |
account_num I Loan_Details |
loan_amount | Loan_Details |
interest_rate I Loan_Details |
Valued Guide
Posts: 2,175

Re: PROC CONTENTS of entire library with all variables detail

proc contents data= yourlib._all_ noprint out= a_data_set ; run;

ods tagsets.excelxp file= '!temp\contents.xml' ;
proc print data= a_data_set noobs ;
run ;
ods tagsets.excelxp close ;

then open that file.
If in a windows dialog box, just try
%temp%\contents.xml
If you run your code in SAS Display Manager, use SAS statement
dm 'winexecfile "!temp\contents.xml" ' ;
Solution
‎01-24-2017 09:24 AM
Super User
Posts: 3,112

Re: PROC CONTENTS of entire library with all variables detail

[ Edited ]

Another way is this:

/* Note that value of libname is UPPERCASE */
proc sql;
create table columns as
select name as variable
,memname as table_name
from dictionary.columns
where libname = 'WORK'
;
quit;

/* SAS 9.4 or later */
ods excel file="c:\temp\variables.xlsx" style=minimal;
proc print data=columns;
run;
ods excel close;

/* earlier versions, using SAS/ACCESS to PC Files */
PROC EXPORT data = columns
OUTFILE = 'variables.xls' 
DBMS = EXCEL REPLACE;
SHEET='VARLIST'; 
RUN;
Senior User
Posts: 1

Re: PROC CONTENTS of entire library with all variables detail

This works the best, I ignored the Proc Export statement. Also important that you pay attention to the case of the libname - in this example it is capitalized and that is the only way it worked for me.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 21852 views
  • 1 like
  • 4 in conversation