The metadata for datasets can be obtained using the DATASETS procedure with he CONTENTS statement and directing the output to a dataset. Use ODS TAGSETS.EXCELXP (or ODS EXCEL in SAS® 9.4) to direct the output to a workbook using PROC PRINT for a simple listing report.
Use options to create a separate worksheet for each dataset in the library.
proc datasets lib = sashelp nolist ; contents data = _all_ out = work.dsets ; quit ; proc format ; value vartyp 1 = 'Num ' 2 = 'Char' ; run ; ods tagsets.excelxp file = 'C:\documents\datasets.xls'
options (sheet_label = ' ' sheet_interval = 'bygroup') ; proc print data = work.dsets noobs ; by memname ; pageby memname ; format type vartyp. ; var memname name type length ; run ; ods _all_ close ;
Metadata can also be obtained using the dictionary tables from PROC SQL. Use ODS along with PROC REPORT for more conrol over style and layout.
proc sql ; create table dsets as select memname ,name ,propcase(type) as type label = 'Type' ,length ,lowcase(format) as format label = 'Format' from dictionary.columns where libname = 'SASHELP' order by memname ; quit ; ods tagsets.excelxp file = 'C:\documents\datasets.xls'
options (sheet_label = 'Dataset '
sheet_interval = 'bygroup'
suppress_bylines = 'on'
) ; proc report data = work.dsets nowd style(report) = [fontsize = 10pt] style(column) = [fontsize = 10pt width = 100] style(header) = [fontsize = 10pt] ; column memname name type length format ; define memname / order ; break after memname / page summarize ; by memname ; run ; ods _all_ close ;
The ORDER usage type suppresses the repetition of the dataset name in the MEMNAME column; the SUMMARIZE keyword on the BREAK statement causes the analysis variable (LENGTH) to be SUMmed.