Sometimes it may be useful to generate a table listing of all the SAS datasets within a folder (the code below uses the previously assigned libref HRA to refer to this folder) — a sort of table of contents — along with some more detail about the fields within those tables. While this information can be output within SAS using a Proc Contents, you may wish to organize this information differently, keep and drop certain variables, and export it in the form of a multi-sheet Excel workbook. The code below shows how a simple data dictionary can be created using Proc Contents and exported to Excel with Proc Export (or alternatively, the Libname statement). Both of these options for exporting data to Excel were discussed in a previous post. Keep in mind that although the code below only selects a very few of the variables available in SAS, you can modify it to select other variables as well. Try it without the keep= option to see the full range of available variables.

Data Dictionary with Proc Export:

PROC CONTENTS DATA=HRA._all_ memtype=data Out=table_listing (keep=memname Nobs name length format crdate);
RUN;

PROC SQL;
Create table directory as
SELECT distinct memname, nobs, crdate
from table_listing;
QUIT;

PROC EXPORT DATA=directory
OUTFILE=”\\path\Data_Dictionary.xls”
DBMS=EXCEL REPLACE;
SHEET=”directory”;
RUN;

PROC EXPORT DATA=table_listing
OUTFILE=”\\path\Data_Dictionary.xls”
DBMS=EXCEL REPLACE;
SHEET=”table_listing”;
RUN;

Alternate Syntax with Libname Statement:

libname myxls “\\path\Data_Dictionary.xls”; /*does not support replace option*/

PROC CONTENTS DATA=HRA._all_ memtype=data Out=myxls.table_listing (keep=memname Nobs name length format crdate);
RUN;

PROC SQL;
Create table myxls.directory as
SELECT distinct memname, nobs, crdate
from myxls.table_listing;
QUIT;