topic Re: Exporting Excel file from SAS with individual sheet per dataset in SAS Programming

Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 09:18:41 GMT

Hello,

I have the below code whereby I can export multiple datasets as individual attachments in an email. However, I want to export one excel document with each dataset on a seperate sheet. Any ideas?

%let user = tul0crf;
%let dataset=CLUB_MEMBERS;
%let dataset1=/user/&user/CLUB_MEMBERS.txt;
%let dataseta=CLUB_TRANSACTIONS;
%let dataset2=/user/&user/CLUB_TRANSACTIONS.txt;
Data &dataset;
set CLUB_MEMBERS;
run;
proc export data=&dataset
   outfile="&dataset1"
   dbms=tab
   replace; 
   run;
Data &dataseta;
set CLUB_TRANSACTIONS;
run;
proc export data=&dataseta
   outfile="&dataset2"
   dbms=tab
   replace; 
   run;

***update email recipients here***;
filename mymail email 
   To=("cameron@gmail.com" )
   subject="SAS CLUBS REPORT EXPORT"
   attach=("&dataset1" "&dataset2");

data _null_;
   file mymail;
   put 'Hi there,';
   put "Please see the attached files";
   put"Thanks,";
   put"CWR automated message";
 run;

Thanks,

Cam

Re: Exporting Excel file from SAS with individual sheet per dataset

Patrick — Wed, 31 May 2017 09:40:45 GMT

@CamRutherford

Which approach to take depends on your version of SAS.

http://support.sas.com/kb/51/580.html

The code creating the Excel Workbook could be as simple as:

%let myexcel=c:\temp\myexcel.xlsx;

/* delete excel from previous runs */
x "del /q ""&myexcel""";

/* write SAS tables to Excel tabs */
libname myxlsx xlsx "&myexcel";
proc  datasets lib=myxlsx nolist nowarn;
  copy 
    in=sashelp
    out=myxlsx;
  select 
    class airline;
  run;
quit;
libname myxlsx clear;

Re: Exporting Excel file from SAS with individual sheet per dataset

RW9 — Wed, 31 May 2017 09:35:58 GMT

What SAS version are you using, and what type of file is acceptable? The reason I ask is that there are several methods to get data out to Excel, then proc export as you have, tagsets.excelxp, libname excel - this for instance is 9.4 only. Let me give a quick example of each - I assume in all that all datasets are in lib TEMP:

Proc export:

data _null_;
  set sashelp.vtable (where=(libname="TEMP"));
  if _n_=1 then call execute('proc export datafile="abc.xls" replace; sheet="'||strip(memname)||'||'"; run;');
  else call execute('proc export datafiles="abc.xls" replace; sheet="'||strip(memname)||'"; run;');
run;

Tagsets.excelxp:

data _null_;
  set sashelp.vtable (where=(libname="TEMP")) end=last;
  if _n_=1 then call execute('ods tagsets.excelxp file="abc.xml";');
  call execute('ods tagsets.excelxp options(sheet_name="'||strip(memname)||'");
                proc report data=temp.'||strip(memname)||' nowd; columns _all_; run;');
  if last then call execute('ods tagsets.excelxp close;');
run;

Libname in 9.4:

libname temp excel "abc.xlsx";

proc datasets library=temp;
  copy out=temp;
run;

libname temp clear;

So as you can see, libname is easiest, tagsets create xml output which can be read by Excel, but allow a lot of formatting and such like, and proc export is the most basic. If you have 94 prob best to use libname.

Re: Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 10:03:16 GMT

Hi,

I have now got this...

DATA PRDSALE;
     SET SASHELP.PRDSALE;
     DIFFERENCE = ACTUAL-PREDICT;
  RUN;
PROC SORT DATA=PRDSALE; 
BY COUNTRY REGION DIVISION YEAR; 
RUN; 
QUIT;
ods listing close;
ods tagsets.excelxp file='test.xml' style=statistical
options(auto_subtotals='yes' 
default_column_width='7, 10, 10, 7, 7'
frozen_rowheaders='yes' 
sheet_interval='none' 
sheet_name='Canada'
autofilter='all' 
autofilter_table='2');

  proc print data=prdsale noobs label split='*';
     where country eq 'CANADA' and year eq 1993;
     id country region division;
     var prodtype product quarter month year;
     sum predict / style={tagattr='format:Currency'};
     sum actual / style={tagattr='format:Currency'};
     sum difference / style={tagattr='format:Currency formula:RC[-1]-RC[-2]'};
     label prodtype = 'Product*Type'
           predict  = 'Predicted*Sales'
           actual   = 'Actual*Sales';
  run; quit;
ods tagsets.excelxp options(sheet_interval='none' sheet_name='Germany');
  proc print data=prdsale noobs label split='*';
     where country eq 'GERMANY' and year eq 1993;
     id country region division;
     var prodtype product quarter month year;
     sum predict / style={tagattr='format:Currency'};
     sum actual / style={tagattr='format:Currency'};
     sum difference / style={tagattr='format:Currency formula:RC[-1]-RC[-2]'};
     label prodtype = 'Product*Type'
           predict  = 'Predicted*Sales'
           actual   = 'Actual*Sales';
  run; quit;
  ods tagsets.excelxp close;
  ods listing;

However I want to be able to send the excel file as an email using the below...

***update email recipients here***;
filename mymail email 
   To=("cameron@gmail.com" )
   subject="SAS CLUBS REPORT EXPORT"
   attach=("&dataset1" "&dataset2");

data _null_;
   file mymail;
   put 'Hi there,';
   put "Please see the attached files";
   put"Thanks,";
   put"CWR automated message";
 run;

Re: Exporting Excel file from SAS with individual sheet per dataset

RW9 — Wed, 31 May 2017 10:11:30 GMT

Well, you don't need two proc reports, you can do:

ods tagsets.excelxp file=... options(sheet_interval="bygroup");

proc report data=...;
  by country;
  where year=1993;
...
run;

ods tagsets.excelxp close;

So use the by group processing within one proc report.

As for your question on how to attach, I am not sure what you ask, can you not just attach the created file:

   attach=("<path>\test.xml");

Re: Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 10:20:29 GMT

@RW9 @Patrick

Thanks both!

I now have one final problem...when I get the email with the attachment it says the file is corrupt - how can I fix this?

DATA PRDSALE;
     SET SASHELP.PRDSALE;
     DIFFERENCE = ACTUAL-PREDICT;
  RUN;
PROC SORT DATA=PRDSALE; 
BY COUNTRY REGION DIVISION YEAR; 
RUN; 
QUIT;

ods tagsets.excelxp file='test.xls' options(sheet_interval="bygroup");

proc report data=PRDSALE;
  by country;
  where year=1993;
run;
ods tagsets.excelxp close;
                                        
FILENAME SEND EMAIL                                                                              
     TO      =  'cameron@test.com'                                  
     SUBJECT =  'MULTI-SHEET'                             
	attach=("test.xls");                       
     ;                                                              
  DATA _NULL_;                                                      
     FILE SEND ;                                                    
     PUT 'PLEASE RECEIVE THE ATTACHED. -- REGARDS,';                
RUN ;

Re: Exporting Excel file from SAS with individual sheet per dataset

Patrick — Wed, 31 May 2017 10:22:55 GMT

@CamRutherford

Hmmm.... First thing I'd do is to open the file from the disk location where you've created it. Is Excel also telling you that this file is corrupt.

Re: Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 10:27:45 GMT

When I open the file it says: 'The file you are trying to open, 'tmpE3AF.tmp.xls', is in a different format than specified by the file extension. Verify thatthe file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?'

I open it and it's fine, but when I send by an email it doesn't like it.

Re: Exporting Excel file from SAS with individual sheet per dataset

RW9 — Wed, 31 May 2017 10:28:23 GMT

Yes, its likely because you have called the file XLS - which is old Office binary file type, but your actually creating XML output. You should really call the file:

test.xml

But you can if need be call it test.xlsx (however you will still get messages from Excel).

It basically telling you the file format is not as expected.

Re: Exporting Excel file from SAS with individual sheet per dataset

Patrick — Wed, 31 May 2017 10:35:17 GMT

@CamRutherford

Call your file in your code test.xlsx and try again.

BTW: If you're using a current SAS version then use ODS EXCEL. That's the preferred ods destination for interfacing with Excel.

http://support.sas.com/documentation/cdl/en/odsug/69832/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04zeur27rv.htm

Re: Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 10:32:46 GMT

Hi,

I tried xlsx and it says its corrupted still.

Cheers

Re: Exporting Excel file from SAS with individual sheet per dataset

Patrick — Wed, 31 May 2017 10:36:27 GMT

What SAS version are you using? Try ODS EXCEL if possible.

Re: Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 10:37:18 GMT

@Patrick @RW9

How can I amend to use ODS Excel?

Re: Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 10:38:17 GMT

Can you show me the equivalent of what I have as ODS excel please?

Re: Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 10:39:05 GMT

SAS Version 6.1 M1HF7 (6.100.0.4234) (64-bit)

Re: Exporting Excel file from SAS with individual sheet per dataset

Patrick — Wed, 31 May 2017 10:43:28 GMT

@CamRutherford

ODS EXCEL - give me a sec

SAS version 6.1?!? That must be the EG client version and not the version of the SAS server.

Please run the following

proc setinit;run;

And then post what you find in the log under:

Current version: <and here the version>

Re: Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 10:53:18 GMT

@Patrick

Yes its Enterprise Guide.

I don't have anything that says 'current version'

Re: Exporting Excel file from SAS with individual sheet per dataset

Patrick — Wed, 31 May 2017 11:02:04 GMT

@CamRutherford

For ODS EXCEL basically the same code than what you've posted (see below). This will only work with a current version of SAS.

DATA PRDSALE;
     SET SASHELP.PRDSALE;
     DIFFERENCE = ACTUAL-PREDICT;
  RUN;
PROC SORT DATA=PRDSALE; 
BY COUNTRY REGION DIVISION YEAR; 
RUN; 
QUIT;

ods excel file='c:\temp\test.xlsx' options(sheet_interval="bygroup");

proc report data=PRDSALE;
  by country;
  where year=1993;
run;
ods excel close;

I could replicate the issue you've raised with ODS TAGSET.EXCELXP. Things work for me without issues when using ODS EXCEL.

Re: Exporting Excel file from SAS with individual sheet per dataset

Patrick — Wed, 31 May 2017 10:58:52 GMT

@CamRutherford

You need to run the following code out of EG in a program window.

proc setinit;run;

Then check the log. That will give you the version of SAS on the server where your SAS code executes.

SAS EG is only the client. What SAS syntax will work depends on the version of your SAS server which executes the code and not on the version of the client.

Re: Exporting Excel file from SAS with individual sheet per dataset

CamRutherford — Wed, 31 May 2017 11:04:06 GMT

@Patrick

Thanks.

I don't understand how I can run the below code and then able to download the excel file and view perctly fine but then when sending the email it doesn't like the file I have and tells me it's corrupt in all different file extensions.

DATA PRDSALE;
     SET SASHELP.PRDSALE;
     DIFFERENCE = ACTUAL-PREDICT;
  RUN;
PROC SORT DATA=PRDSALE; 
BY COUNTRY REGION DIVISION YEAR; 
RUN; 
QUIT;

ods tagsets.excelxp file='test.xml' options(sheet_interval="bygroup");

proc report data=PRDSALE;
  by country;
  where year=1993;
run;
ods tagsets.excelxp close;
    
 
FILENAME SEND EMAIL                                                                              
     TO      =  'cameron.rutherford@rci.com'                                  
     SUBJECT =  'MULTI-SHEET'                             
	attach=("test.xml");                       
     ;                                                              
  DATA _NULL_;                                                      
     FILE SEND ;                                                    
     PUT 'PLEASE RECEIVE THE ATTACHED. -- REGARDS,';                
RUN ;

I've just ran ...

proc setinit;run;

and I get this...

proc setinit;run;

NOTE: PROCEDURE SETINIT used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              339.65k
      OS Memory           35992.00k
      Timestamp           05/31/2017 07:00:05 AM
      Step Count                        83  Switch Count  18
      Page Faults                       0
      Page Reclaims                     62
      Page Swaps                        0
      Voluntary Context Switches        35
      Involuntary Context Switches      9
      Block Input Operations            544
      Block Output Operations           0
      
Original site validation data
Site name:    'SITE XXXXX'.
Site number:  XXXXX.
Expiration:   29JUN2017.
Grace Period:  45 days (ending 13AUG2017).
Warning Period: 45 days (ending 27SEP2017).
System birthday:   26JUL2016.
Operating System:   LIN X64 .