As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Beautiful PROC CONTENTS Output Using the ODS Excel Destination

From sasCommunity
Jump to: navigation, search

Abstract

A member of the Census Bureau’s in-house SAS® users group asked how to export the output of PROC CONTENTS (variable name, type, length, and format) from several Oracle database tables within the same database to separate worksheets in an Excel file. You can use the _all_ keyword to work with all the tables within a library. The ODS Excel destination, which is production in SAS 9.4 maintenance release 3, displays the output beautifully.


Online Materials

View the pdf for the paper.

You can also see the PowerPoint presentation.


Sample Code and Output

Example using Oracle database

This is the main example discussed in the paper:

libname asj oracle
  user=asjview
  password=asjview
  path=jailprod.world
  schema=asjdba
  access=readonly;
run;
 
* The ODS output statement tells SAS to route the information from the 
* variables table in PROC CONTENTS to the AllOracleVarOut data set.  We can
* use the by statement with PROC PRINT to show each data set's variables
* on a separate sheet. ;
 
ods output variables=AllOracleVarOut; 
 
* Hat tip to the Base SAS(R) 9.4 Procedures Guide, Third Edition,
* Example 1: Using PROC CONTENTS to Extract Only Attributes from Data Sets
* for showing how to send just one part of PROC CONTENTS to a data set,
* and the use of the _all_ keyword to work with all data sets in the library. ;
* http://support.sas.com/documentation/cdl/en/proc/67327/HTML/default/viewer.htm#n0gua2u8gtv7irn1n8rp9ro2iu8p.htm ;
 
proc contents data=asj._all_;
run;
 
proc sort data=AllOracleVarOut;
  by member num;
run;
 
options nobyline;  * Suppresses the MEMBER=____ text above each table in output;
 
*** #BYVAL(member) is telling SAS to use the by value of the member variable as the sheet 
*** name.  the result is that each tab is named for a table in the Oracle database.  ;
 
ODS EXCEL FILE="H:\SESUG 2015\all_variables_from_Oracle_database.xlsx"
  options(sheet_name="#BYVAL(member)"
          embedded_titles='yes');
 
proc print data=AllOracleVarOut noobs;
  by member;
  pageby member;
  title "Variables in #BYVAL(member) table";
run;
 
ODS EXCEL CLOSE;

The sample code produces this Excel file.

Example using SASHELP library

This code sample can be used by any SAS user. Remember to update the path on the ODS Excel statement!

* The ODS output statement tells SAS to route the information from the 
* variables table in PROC CONTENTS to the allvarout data set.  We can
* use the by statement with PROC PRINT to show each data set's variables
* on a separate sheet. ;
 
ODS OUTPUT variables=allvarout;
 
* Hat tip to the Base SAS(R) 9.4 Procedures Guide, Third Edition,
* Example 1: Using PROC CONTENTS to Extract Only Attributes from Data Sets
* for showing how to send just one part of PROC CONTENTS to a data set,
* and the use of the _all_ keyword to work with all data sets in the library. ;
* http://support.sas.com/documentation/cdl/en/proc/67327/HTML/default/viewer.htm#n0gua2u8gtv7irn1n8rp9ro2iu8p.htm ;
 
* Use memtype=data to exclude views from the results.  SASHELP has several
* views, such as VCOLUMN. ;
 
proc contents data=sashelp._all_ memtype=data;
run;
 
proc sort data=allvarout;
  by member num;
run;
 
options nobyline; * Suppresses the MEMBER=____ text above each table in output;
 
* #BYVAL(member) tells SAS to use the by value of the member variable as
* the sheet name.  The result is that each tab is named for a data set in
* the SASHELP library. ;
 
ODS EXCEL FILE="H:\SESUG 2015\all_variables_in_SASHELP_data_sets.xlsx"
  options(sheet_name="#BYVAL(member)"
          embedded_titles='yes');
 
proc print data=allvarout noobs;
  by member;
  pageby member;
  title "Variables in #BYVAL(member) data set";
run;
 
ODS EXCEL CLOSE;

Note that the number of SAS data sets in the SASHELP library depends on the products installed.

This example produces this Excel file.

Other Resources

When I presented the talk at SESUG, people asked which ExcelXP tagset options work with the ODS Excel destination. Chevell Parker listed the options in his SAS Global Forum 2014 paper. See Table 2. New and Deprecated Options in the Excel Destination on page 18 of Secrets from a SAS Technical Support Guy: Combining the Power of the SAS® Output Delivery System with Microsoft Excel Worksheets.

LeRoy Bessler gave a talk in June 2015 for the Wisconsin Illinois SAS® Users group and showed some of the options that have been added to the destination since Chevell's talk in 2014. See The New SAS® ODS Excel Destination: A User Review and Demonstration for more details.

See the ODS EXCEL Statement section of the SAS(R) 9.4 Output Delivery System: User's Guide, Fourth Edition for the syntax available in SAS 9.4 maintenance release 3.

Contact Info

Please check out my user page. You can also email me.