PROC CONTENTS of entire library with all variables detail

[chandler]

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

[chandler]

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]

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 · Accepted solution]

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]

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.