I have the attached code in a codenode and when I run it the code create an XLSX file that I can read directly using Excel2010. But when it attempts to attach the file to an email, the email is produced but the attachment give an error message
options symbolgen mlogic;
%let TitleDate = %sysfunc(intnx(Month,%sysfunc(date()),-1,B),yymmd7. );
%put &Titledate;
%let Filename = SeenByEMRes.xlsx;
%put &Filename;
%let FullFileName = &TitleDate. &FileName;
%PUT &FullFileName;
PROC SQL;
CREATE TABLE WORK.SeenByEMResident AS
SELECT t1.NAME,
/* Visits */
(count ( t1.PAT_ENC_CSN_ID )) LABEL="Visits" AS Visits
FROM WORK.QUERY_FOR_PAT_ENC_HSP_0003 t1
GROUP BY t1.NAME;
QUIT;
proc export data=SeenByEMResident
outfile="\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\&fullfilename."
dbms=xlsx replace ;
sheet="Residents";
run ;
%PUT &fullfilename;
ods pdf close;
filename mailit email to="mcmulj@mmc.org"
cc="shryor@mmc.org"
bc=""
from="mcmulj@mmc.org"
attach="\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\&FullFileName."
/* multiple attachments go between parentheses and are separated by spaces */
subject="Resident Volume &Titledate";
data _null_;
file mailit;
put 'THIS IS AN AUTO GENERATED E-MAIL' ;
put;
put 'Hello,';
put;
put;
put' Please find attached an excel file with your results for:';
put;
put "----> Residency Volume For &Titledate <---";
put;
put;
put 'Joel McMullin';
put 'Center for Performance Improvement';
put 'Maine Medical Center';
put '(207) 662-6735';
run;
filename mailit clear;
And this is the log that was produced:
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET SYSLAST=WORK.QUERY_FOR_PAT_ENC_HSP_0003;
4 %LET _CLIENTTASKLABEL='Program1';
5 %LET _CLIENTPROJECTPATH='R:\EM Residents\Seen_By_EMRes\MH_MMC_ASAP_Seen_By_EMRes.egp';
6 %LET _CLIENTPROJECTNAME='MH_MMC_ASAP_Seen_By_EMRes.egp';
7 %LET _SASPROGRAMFILE=;
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=ACTIVEX;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
13 ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/5.1/Styles/HtmlBlue.
13 ! css") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "D:\sastemp\_TD7712_SASMETA2_\Prc2/"
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
14
15 GOPTIONS ACCESSIBLE;
16 options symbolgen mlogic;
17 %let TitleDate = %sysfunc(intnx(Month,%sysfunc(date()),-1,B),yymmd7. );
18 %put &Titledate;
SYMBOLGEN: Macro variable TITLEDATE resolves to 2013-07
2013-07
19 %let Filename = SeenByEMRes.xlsx;
20 %put &Filename;
SYMBOLGEN: Macro variable FILENAME resolves to SeenByEMRes.xlsx
SeenByEMRes.xlsx
21 %let FullFileName = &TitleDate. &FileName;
SYMBOLGEN: Macro variable TITLEDATE resolves to 2013-07
SYMBOLGEN: Macro variable FILENAME resolves to SeenByEMRes.xlsx
22 %PUT &FullFileName;
SYMBOLGEN: Macro variable FULLFILENAME resolves to 2013-07 SeenByEMRes.xlsx
2013-07 SeenByEMRes.xlsx
23 PROC SQL;
24 CREATE TABLE WORK.SeenByEMResident AS
25 SELECT t1.NAME,
26 /* Visits */
27 (count ( t1.PAT_ENC_CSN_ID )) LABEL="Visits" AS Visits
28 FROM WORK.QUERY_FOR_PAT_ENC_HSP_0003 t1
29 GROUP BY t1.NAME;
NOTE: Table WORK.SEENBYEMRESIDENT created, with 19 rows and 2 columns.
30 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
31
32 proc export data=SeenByEMResident
33 outfile="\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\&fullfilename.
33 ! "
SYMBOLGEN: Macro variable FULLFILENAME resolves to 2013-07 SeenByEMRes.xlsx
2 The SAS System 13:18 Monday, August 26, 2013
34 dbms=xlsx replace ;
35 sheet="Residents";
36 run ;
NOTE: The export data set has 19 observations and 2 variables.
NOTE: "\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\2013-07 SeenByEMRes.xlsx"
file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
37
38 %PUT &fullfilename;
SYMBOLGEN: Macro variable FULLFILENAME resolves to 2013-07 SeenByEMRes.xlsx
2013-07 SeenByEMRes.xlsx
39 ods pdf close;
40 filename mailit email to="mcmulj@mmc.org"
41 cc="shryor@mmc.org"
42 bc=""
43 from="mcmulj@mmc.org"
SYMBOLGEN: Macro variable FULLFILENAME resolves to 2013-07 SeenByEMRes.xlsx
44 attach="\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\&FullFileName."
45 /* multiple attachments go between parentheses and are separated by spaces */
46
SYMBOLGEN: Macro variable TITLEDATE resolves to 2013-07
47 subject="Resident Volume &Titledate";
48
49
50 data _null_;
51 file mailit;
52 put 'THIS IS AN AUTO GENERATED E-MAIL' ;
53 put;
54 put 'Hello,';
55 put;
56 put;
57 put' Please find attached an excel file with your results for:';
58 put;
SYMBOLGEN: Macro variable TITLEDATE resolves to 2013-07
59 put "----> Residency Volume For &Titledate <---";
60 put;
61 put;
62 put 'Joel McMullin';
63 put 'Center for Performance Improvement';
64 put 'Maine Medical Center';
65 put '(207) 662-6735';
66 run;
NOTE: The file MAILIT is:
E-Mail Access Device
Message sent
To: "mcmulj@mmc.org"
Cc: "shryor@mmc.org"
3 The SAS System 13:18 Monday, August 26, 2013
Bcc: ""
Subject: Resident Volume 2013-07
Attachments: "\\sasmeta2\projects\mmc\warehouse\wdata\DataSources\MailItAttach\ED\2013-07
SeenByEMRes.xlsx"
NOTE: 14 records were written to the file MAILIT.
The minimum record length was 0.
The maximum record length was 58.
NOTE: DATA statement used (Total process time):
real time 2.88 seconds
cpu time 0.00 seconds
67 filename mailit clear;
NOTE: Fileref MAILIT has been deassigned.
68
69
70
71
72
73 GOPTIONS NOACCESSIBLE;
74 %LET _CLIENTTASKLABEL=;
75 %LET _CLIENTPROJECTPATH=;
76 %LET _CLIENTPROJECTNAME=;
77 %LET _SASPROGRAMFILE=;
78
79 ;*';*";*/;quit;run;
80 ODS _ALL_ CLOSE;
81
82
83 QUIT; RUN;
84
I have also attached a screenshot of the error message that I get when I attempt to open the attached xlsx file
Anyone got any ideas what I'm doing wrong???
Thanks, Dick Shryock
Click image to register for webinar
