90 users online | 90 Guests and 0 Registered

Can I create an Excel Workbook containing the metadata for all datasets in a library?


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.

Author:
Alan D Rudland
Revision:
1.3
Average rating:0 (0 Votes)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.

Records in this category

Tags