BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chandler
Fluorite | Level 6
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
1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

4 REPLIES 4
chandler
Fluorite | Level 6
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 |
Peter_C
Rhodochrosite | Level 12
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" ' ;
SASKiwi
PROC Star

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;
AlexLab
Calcite | Level 5
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 59084 views
  • 2 likes
  • 4 in conversation