/* Get latest copy of tagset from SAS web site */
filename tagset http
   "%lowcase(
   HTTP://SUPPORT.SAS.COM:80/RND/BASE/TOPICS/ODSMARKUP/EXCLTAGS.TPL
   )";
%include tagset / nosource2;
filename tagset clear;

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

/* Tell SAS where to send output */
ods tagsets.excelxp file=xlsfile record_separator=none
                    style=sansprinter;
ods listing close;

/* Print the first sheet */
ods tagsets.excelxp
      options(sheet_name='Raw Data'
      frozen_headers='1'
      row_repeat='1'
      autofilter='1-5'
      );
title 'Raw Data';

proc print data=sashelp.prdsale noobs;
   var country region division prodtype product year quarter month
       actual predict;
run;

/* Print the second sheet, triggered by a new ODS statement  */
/* to the ExcelXP tagset without a new FILE=.                */
ods tagsets.excelxp
      options(sheet_name='Sales Summary'
      autofilter='3'
      );

proc report data=sashelp.prdsale missing nowindows nocenter;
   column country region prodtype actual predict;
   define country  / group 'Country';
   define region   / group 'Region';
   define prodtype / group 'Product Type';
   define actual   / sum 'Actual Sales';
   define predict  / sum 'Predicted Sales';
run;

ods _all_ close;
ods listing;

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

filename email email
               attach=("&REPORTDSN."
                       name='EmailXLS'
                       extension='xls'
                       lrecl=8196)
               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;
