DSNS to Excel

A very common task is to copy SAS data sets to Excel. The below macro does just that using the ODS EXCEL statement.

Named parameters are used to accept the input values. The DSNS= parameter accepts one or more one or two level SAS data sets separated by spaces. The %isblank() macro that can be found here is used to look for blank values.

A number of basic integrity checks are performed such as was an output filename supplied and does the path of that file exist? Each of the supplied data sets are examined to ensure they exist before the Excel file is created. The member name (second value in a two level name) is used as the name of the sheet in Excel. This is accomplished by using the %INDEX macro function in conjunction with the SIGN function which is incremented by 1.

/**************************************************************************
*     Program: dsnstoexcel.sas
*      Author: Tom Bellmer
*     Created: 20180503 
* SAS Version: SAS 9.4 (TS1M3)
*     Purpose: Write DSNs to Excel tabs
*       Usage: %dsnstoexcel(dsns=, outfile=, style=) ;
*       Notes: EX: %dsnstoexcel(dsns=sashelp.cars sashelp.class, outfile=/myfolder/x.xlsx ) ;
*
*FL-YYYYMMDD                           Description
*----------- -------------------------------------------------------------
*TB-20180508 Added option for style. Turned on 
*            autofilter, frozen headers and rowheaders
**************************************************************************/
 
%macro dsnstoexcel( 
     dsns    = 
   , outfile = 
   , style   = excel
) ;
 
   %local
      i
      totaldsns 
      jobstarttime 
      slash
      dsn
      position
      name
   ;
 
   %if %isblank( &dsns. ) and %isblank( &outfile. ) %then %do ;
      %put %str(E)RROR: No values passed.  Please use %nrstr( %dsns2excel(dsns=, outfile=) ) syntax. ;
      %return ;
   %end ;
 
   %if %isblank( &outfile. ) %then %do ;
      %put %str(E)RROR: Must pass in values for OUTFILE parameter. ;
      %return ;
   %end ;
 
   %if &sysscp. = WIN %then %do ; 
      %let slash = %str(\) ; 
      options dev = activex ;
   %end ;
   %else %do ; 
      %let slash = %str(/) ; 
      options dev = actximg ;
   %end ;
 
   %if not %sysfunc( fileexist( %substr( &outfile., 1, %sysfunc( find( &outfile, &slash., -999 ) ) ) ) ) %then %do ;
      %put %str(E)RROR: the OUTFILE folder does not exist. ;
      %return ;
   %end ;
 
   %let totaldsns = %sysfunc( countw( &dsns., %str( ) ) ) ;
   %if &totaldsns = 0 %then %do ;
      %put %str(E)RROR: must pass in values for DSNS parameter. ;
      %return ;
   %end ;
 
   %do i = 1 %to &totaldsns. ;
      %let dsn = %scan( &dsns., &i., %str( ) ) ;
      /* verfiy the data sets actually exist */
      %if not %sysfunc( exist( &dsn. ) ) %then %do ;
         %put %str(E)RROR: Data set &dsn. does not exist. ;
         %return ;
      %end ;
   %end ;
 
   %let jobstarttime = %sysfunc( datetime() ) ;
   
   ods _all_ close ; 
   ods results off ;
   ods excel file = "&outfile." style = &style. ;
      %do i = 1 %to &totaldsns. ;
         %let dsn = %scan( &dsns., &i., %str( ) ) ;
         %let position = %eval( %sysfunc( sign( %index( &dsn, . ) ) ) + 1 ) ;
         %let name = %scan(&dsn., &position., . ) ;
 
         ods excel options( 
            autofilter        = 'all'     /* turn on Auto filtering for all columns */
            frozen_headers    = 'on'      /* Freeze first row of data to always see headers */
            frozen_rowheaders = '1'       /* Freeze first column to always see */
            sheet_name        = "&name."  /* name of the sheet */
         ) ;
 
         proc print data = &dsn. noobs ;
         run ;         
      %end ;
   ods excel close ;
   ods results on ;
 
   data _null_ ;
      put "******************************************************************************" ;
      put "     Macro Name: &sysmacroname." ;
      put "     Total DSNs: &totaldsns." ;
      put "Output Filename: &outfile." ;
      put "Completion Time: %left( %sysfunc( datetime(), datetime22. ) )" ;
      put "   Elapsed Time: %left( %sysfunc( putn( %sysevalf( %sysfunc( datetime() ) - &jobstarttime. ), mmss12.3 ) ) )" ;
      put "******************************************************************************" ;
   run ;
%mend ;
 
/*EOF: dsnstoexcel.sas */