DATA Step, Macro, Functions and more

Exporting Excel file from SAS with individual sheet per dataset

Reply
Contributor
Posts: 60

Exporting Excel file from SAS with individual sheet per dataset

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

Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to CamRutherford

@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;
Super User
Super User
Posts: 7,997

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

Posted in reply to CamRutherford

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. 

Contributor
Posts: 60

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

Posted in reply to CamRutherford

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; 
Super User
Super User
Posts: 7,997

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

Posted in reply to CamRutherford

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");

 

Contributor
Posts: 60

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

Posted in reply to CamRutherford

@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 ;          
Respected Advisor
Posts: 4,173

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

Posted in reply to CamRutherford

@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.

Super User
Super User
Posts: 7,997

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

Posted in reply to CamRutherford

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.

Contributor
Posts: 60

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

Posted in reply to CamRutherford

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.

 

 

Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to CamRutherford

@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#p09n5pw9ol0897n1qe04...

 

Contributor
Posts: 60

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

Posted in reply to CamRutherford

Hi, 

 

I tried xlsx and it says its corrupted still.

 

Cheers

Respected Advisor
Posts: 4,173

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

Posted in reply to CamRutherford

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

Contributor
Posts: 60

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

Posted in reply to CamRutherford

@Patrick @RW9

 

How can I amend to use ODS Excel?

Contributor
Posts: 60

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

Posted in reply to CamRutherford

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

Contributor
Posts: 60

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

Posted in reply to CamRutherford

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

Ask a Question
Discussion stats
  • 28 replies
  • 158 views
  • 0 likes
  • 3 in conversation