topic Export to email in SAS Programming

Export to email

Ronein — Tue, 27 Dec 2022 07:29:15 GMT

Hello

I create a simple report and want to send it to some people over email .

I want to attach XLSX file to the email.

I am using proc export to XLSX file and I see that the file was created and I can open the file .

The export to email was done well and I see that the email was sent .

The problem is that I cannot open the XLSX file from the attachment in email.

Why did it happen?

Why can't I open the XLSX file from email?

Here is the message I get

proc sql;
create table Report1 as
select make,
       sum(Invoice) as Total_Invoice format=comma23.,
	   count(*) as nr_rows format=comma23.,
	   calculated Total_Invoice/(select  sum(Invoice) as Total_Invoice  from sashelp.cars Where Origin='Europe') as PCT_Total_Invoice format=percent8.1,
	   calculated nr_rows/(select  count(*) as Total_nr  from sashelp.cars Where Origin='Europe') as PCT_nr_rows format=percent8.1
from sashelp.cars
Where Origin='Europe'
group by make
order by Total_Invoice desc
;
quit;

proc export data=Report1 dbms=xlsx  outfile="/usr/local/SAS/SASUsers/LabRet/UserDir/Report1.XLSX"  replace;
run;

data _null_;
file sendit email
from="<Dave.gdansk@gmail.com>"
to=("<Joe.London@gmail.com>")
cc=("<Yelena.Frucht@gmail.com>","<Bob.Bon@gmail.com>")
subject="Important Document"
importance="High"
attach=("/usr/local/SAS/SASUsers/LabRet/UserDir/Report1.XLSX");
put "Please find attached the report";
put;
put "Thanks!";
run;

Re: Export to email

Kurt_Bremser — Tue, 27 Dec 2022 07:28:13 GMT

Please post the complete log from all three steps.

Re: Export to email

Ronein — Tue, 27 Dec 2022 07:35:27 GMT

Here is the Log

1                                                          The SAS System                           08:30 Tuesday, December 27, 2022

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program (6)';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         proc sql;
27         create table Report1 as
28         select make,
29                sum(Invoice) as Total_Invoice format=comma23.,
30         	   count(*) as nr_rows format=comma23.,
31         	   calculated Total_Invoice/(select  sum(Invoice) as Total_Invoice  from sashelp.cars Where Origin='Europe') as
31       ! PCT_Total_Invoice format=percent8.1,
32         	   calculated nr_rows/(select  count(*) as Total_nr  from sashelp.cars Where Origin='Europe') as PCT_nr_rows
32       ! format=percent8.1
33         from sashelp.cars
34         Where Origin='Europe'
35         group by make
36         order by Total_Invoice desc
37         ;
NOTE: SAS threaded sort was used.
NOTE: Compressing data set WORK.REPORT1 increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.REPORT1 created, with 10 rows and 5 columns.

38         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      user cpu time       0.02 seconds
      system cpu time     0.00 seconds
      memory              7075.92k
      OS Memory           33712.00k
      Timestamp           12/27/2022 09:30:41 AM
      Step Count                        93  Switch Count  5
      Page Faults                       0
      Page Reclaims                     467
      Page Swaps                        0
      Voluntary Context Switches        67
2                                                          The SAS System                           08:30 Tuesday, December 27, 2022

      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

39         
40         proc export data=Report1 dbms=xlsx  outfile="/usr/local/SAS/SASUsers/LabRet/UserDir/Report1.XLSX"  replace;
41         run;

NOTE: The export data set has 10 observations and 5 variables.
NOTE: "/usr/local/SAS/SASUsers/LabRet/UserDir/Report1.XLSX" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              1673.12k
      OS Memory           29996.00k
      Timestamp           12/27/2022 09:30:41 AM
      Step Count                        94  Switch Count  0
      Page Faults                       0
      Page Reclaims                     210
      Page Swaps                        0
      Voluntary Context Switches        30
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

42         
43         data _null_;
44         file sendit email
45         from="<Bob.Lark@AAA.co.il>"
46         to=("<Bob.Lark@AAA.co.il>")
47         cc=("<Bob.Lark@AAA.co.il>","<Bob.Lark@AAA.co.il>")
48         subject="Important Document"
49         importance="High"
50         attach=("/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/cars_Report.XLSX" );
51         put "Please find attached the file";
52         put;
53         put "Thanks!";
54         run;

NOTE: The file SENDIT is:
      E-Mail Access Device

Message sent
      To:          "<Bob.Lark@AAA.co.il>"
      Cc:          "<Bob.Lark@AAA.co.il>" "<Bob.Lark@AAA.co.il>"
      Bcc:         
      Subject:     Important Document
      Attachments: ( "/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/cars_Report.XLSX" ) 
NOTE: 3 records were written to the file SENDIT.
      The minimum record length was 0.
      The maximum record length was 29.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      user cpu time       0.01 seconds
      system cpu time     0.01 seconds
3                                                          The SAS System                           08:30 Tuesday, December 27, 2022

      memory              333.75k
      OS Memory           29348.00k
      Timestamp           12/27/2022 09:30:41 AM
      Step Count                        95  Switch Count  10
      Page Faults                       0
      Page Reclaims                     17
      Page Swaps                        0
      Voluntary Context Switches        86
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

55         
56         
57         GOPTIONS NOACCESSIBLE;
58         %LET _CLIENTTASKLABEL=;
59         %LET _CLIENTPROCESSFLOWNAME=;
60         %LET _CLIENTPROJECTPATH=;
61         %LET _CLIENTPROJECTPATHHOST=;
62         %LET _CLIENTPROJECTNAME=;
63         %LET _SASPROGRAMFILE=;
64         %LET _SASPROGRAMFILEHOST=;
65         
66         ;*';*";*/;quit;run;
67         ODS _ALL_ CLOSE;
68         
69         
70         QUIT; RUN;
71

Re: Export to email

Ronein — Tue, 27 Dec 2022 07:47:24 GMT

I found the solution:)

Need to add statement

content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

The question is why this statement is essential?

proc sql;
create table Report1 as
select make,
       sum(Invoice) as Total_Invoice format=comma23.,
	   count(*) as nr_rows format=comma23.,
	   calculated Total_Invoice/(select  sum(Invoice) as Total_Invoice  from sashelp.cars Where Origin='Europe') as PCT_Total_Invoice format=percent8.1,
	   calculated nr_rows/(select  count(*) as Total_nr  from sashelp.cars Where Origin='Europe') as PCT_nr_rows format=percent8.1
from sashelp.cars
Where Origin='Europe'
group by make
order by Total_Invoice desc
;
quit;

proc export data=Report1 dbms=xlsx  outfile="/usr/local/SAS/SASUsers/LabRet/UserDir/Report1.XLSX"  replace;
run;

data _null_;
file sendit email
from="<Bob.Dave@gmail.com>"
to=("<Bob.Dave@gmail.com>")
cc=("<Bob.Dave@gmail.com>","<Bob.Dave@gmail.com>")
subject="Important Document"
importance="High"
attach=("/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/cars_Report.XLSX"
content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
put "Please find attached the file";
put;
put "Thanks!";
run;

Re: Export to email

SASKiwi — Tue, 27 Dec 2022 08:51:28 GMT

Normally the OS will try to choose an appropriate application to open an attachment based on the extension. However an XLSX spreadsheet is actually a compressed folder of XML-based files so the OS has difficulty figuring out how to handle the attachment, hence the content type identification.

Re: Export to email

Kurt_Bremser — Tue, 27 Dec 2022 12:17:22 GMT

Did you try (before making the change that made it work) to save the attachment manually and then open it from within Excel?

Re: Export to email

Ronein — Tue, 27 Dec 2022 14:04:40 GMT

I didn't try it but anyway people who recieve the email should be able to open the xlsx file via email