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
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;
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.
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;
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");
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 ;
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.
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.
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.
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.
Hi,
I tried xlsx and it says its corrupted still.
Cheers
What SAS version are you using? Try ODS EXCEL if possible.
Can you show me the equivalent of what I have as ODS excel please?
SAS Version 6.1 M1HF7 (6.100.0.4234) (64-bit)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.