BookmarkSubscribeRSS Feed
CamRutherford
Fluorite | Level 6

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

28 REPLIES 28
Patrick
Opal | Level 21

@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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

CamRutherford
Fluorite | Level 6

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; 
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

CamRutherford
Fluorite | Level 6

@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 ;          
Patrick
Opal | Level 21

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

CamRutherford
Fluorite | Level 6

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.

 

 

Patrick
Opal | Level 21

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

 

CamRutherford
Fluorite | Level 6

Hi, 

 

I tried xlsx and it says its corrupted still.

 

Cheers

Patrick
Opal | Level 21

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

CamRutherford
Fluorite | Level 6

@Patrick @RW9

 

How can I amend to use ODS Excel?

CamRutherford
Fluorite | Level 6

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

CamRutherford
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 28 replies
  • 1190 views
  • 0 likes
  • 3 in conversation