Creating and emailing an Excel workbook on MVS

The sample code below shows how to create an "Excel workbook" on MVS and email it to yourself.

I put "Excel workbook" in quotes because it's not really a native Excel workbook. It's XML which Excel knows how to read. Before distributing a workbook created this way, you should open it in Excel and save it as a native document - recent versions of Excel can read the XML, but not everyone has upgraded.

(Future versions of Excel might use XML as their native format, in which case SAS might be able to create native format Excel files. But that hasn't happened as of spring 2006.)

//EMAILXLS JOB xxxxxx,'Email XLS',NOTIFY=&SYSUID,
//          MSGCLASS=X,CLASS=S
//*
/*ROUTE PRINT FETCH
//*
//S1REPORT EXEC SASPROD,
//            OPTIONS='ls=71 noovp nocenter'
//ODSTEXT   DD DSN=&SYSUID..ODSTEMP.XLS,DISP=(MOD,CATLG,CATLG),
//             LRECL=16392,RECFM=VB,SPACE=(16392,(1000,2000)),
//             UNIT=SYSWRK
//SYSIN DD *,DLM='\\'

/* Get latest copy of tagset from SAS web site */
filename tagset http
   'http://support.sas.com:80/rnd/base/topics/odsmarkup/excltags.tpl';
%include tagset / nosource2;

/* Empty the output file, in case this is a rerun. */
data _null_;
   file odstext old;
run;

ods tagsets.excelxp file=odstext record_separator=none style=sasweb;
ods listing close;

title 'Transfer';

data VolumeExpense;
   length PurchMC ProvMC Type $8.;
   format Cost comma10.2;
   do purchmc = 'Hay', 'Rch', 'Oak';
      do provmc = 'Oak', 'Hay', 'Rch';
         do type = 'Volume', 'Expense';
            cost = round(ranuni(94612)*100, .01);
            output;
         end;
      end;
   end;
run;

/* Print the first sheet */
ods tagsets.excelxp
      options(sheet_name='Output from PRINT'
      frozen_headers='1'
      row_repeat='1'
      );

proc print data=VolumeExpense;
run;

/* Print the second sheet, triggered by a new ODS statement  */
/* to the tagset without a new FILE=.                        */
ods tagsets.excelxp
      options(sheet_name='Output from REPORT'
      frozen_headers='3'
      row_repeat='1-3'
      autofilter='2'
      );

proc report data=VolumeExpense missing nofs nocenter
      completerows completecols;
   column purchmc type provmc, cost cost=totcost;
   define purchmc / group 'Purch MC' width=8 order=data;
   define type    / group 'Type'     width=8 order=data;
   define provmc  / across 'Prov MC' width=8 order=data;
   define Cost    / sum 'Cost';
   define totcost / sum 'Total Cost' width=10 format=comma10.2;
   compute provmc;
      if type = 'Expense' then
         call define(_col_, 'style', 'style={background=yellow}');
   endcomp;
run;

ods _all_ close;
ods listing;

/* Close and free the output file. */            
data _null_;                                      
   length filename $44.;                          
   file odstext mod close=free filename=filename;
   call symput('REPORTDSN', trim(filename));      
run; 

filename email email
               attach=("&REPORTDSN."
                       name='EmailXLS' extension='xls')
               to='Jack.Hamilton@kp.org'
               from='Jack.Hamilton@kp.org'
               subject='Excel test ';

data _null_;

   file email;

   put 'Results are attached.';

run;

filename email clear;

\

A few interesting features of this code:

//ODSTEXT   DD DSN=&SYSUID..ODSTEMP.XLS,DISP=(MOD,CATLG,CATLG),
//             LRECL=16392,RECFM=VB,SPACE=(16392,(1000,2000)),
//             UNIT=SYSWRK

This makes sure that there's a file to which to write the output. DISP=MOD tells the system to use an existing file if there is one, and to create a new one if there isn't.

/* Empty the output file, in case this is a rerun. */
data _null_;
   file odstext old;
run;

This makes sure the file is empty (which it wouldn't be if this were a rerun).

ods tagsets.excelxp
      options(sheet_name='Output from PRINT'
      frozen_headers='1'
      row_repeat='1'
      );

This tells SAS to create a new sheet (in this case, the first one), and specifies the sheet name and some header information.

ods tagsets.excelxp
      options(sheet_name='Output from REPORT'
      frozen_headers='3'
      row_repeat='1-3'
      autofilter='2'
      );

This tells SAS to start the second sheet, and specifies a different sheet name and header information. It also specifies an autofilter on the second column.

/* Close and free the output file. */            
data _null_;                                      
   length filename $44.;                          
   file odstext mod close=free filename=filename;
   call symput('REPORTDSN', trim(filename));      
run; 

This tells SAS to close and free the output data set. In the case of a new data set, it will be catalogued (ordinarily, it wouldn't be catalogued until the job step finished). Use of the FILENAME= option captures the data set name; this allows you to specify the data set name in only one place, the JCL, instead of in both the JCL and the SAS code.

filename email email
               attach=("&REPORTDSN." 
                       name='EmailXLS' extension='xls')
               to='Jack.Hamilton@kp.org'
               from='Jack.Hamilton@kp.org'
               subject='Excel test ';

This sends the newly written file as an attachment to email. Notice that SAS requires the actual catalogued data set name, not a DDname, which is why I had to free the file in the previous data step.

The resulting file is here.