Hi All
I have created below code to export my SAS data set into xlsx file with multiple sheets, file got created and sent email with attachment but xls file unable to open , its says file is corrupted, if we manually transfer xlsx file from SAS server to windows Desktop through WINSCP, file gets opened , but its not opening through email and SAS server locations. here is the code. Appreciate your help on this
libname x xlsx "/workspace/lob5/alm/u441070/xyz.xlsx";
data x.DQ_Pricing_Completeness;
set work.pdq001;
run;
data x.DQ_Pricing_Integrity;
set work.pdq002;
run;
%macro SendEmailFAST;
filename outbox email
to=(&emailTo1)
from=(&emailFrom)
cc=(&emailCC)
bcc=(&emailbcc)
type='text/html'
subject= "Data Quality Run &scn -&dbn &taday"
attach="/workspace/lob5/alm/u441070/xyz.xlsx" ;
ODS html body=outbox style=Harvest; /*BarrettsBlue*/
/* Pricing*/
Title1 color=red H=3 "Data Quality Exceptions Report NVP Pricing Templates for &scn%";
proc report data=Email_Fast_Rep_Pricing ;
columns CNT DQ_Dimension DQRule Field DQCHEK_POINT DQPriority;
define DQPriority / display; /* explicitly specify column usage */
define DQCHEK_POINT / display;
compute DQPriority;
if DQPriority='High' then call define(_col_, "style", "style=[backgroundcolor=Red]");
if DQPriority='Medium' then call define(_col_, "style", "style=[backgroundcolor=coral]");
if DQPriority='Low' then call define(_col_, "style", "style=[backgroundcolor=cyan]");
if DQPriority='No Actions' then call define(_col_, "style", "style=[backgroundcolor=Green]");
endcomp;
compute DQCHEK_POINT;
if DQCHEK_POINT='Fail' then call define (_col_, 'style', 'style=[foreground=Red]');
endcomp;
run;
Title1;
ods _all_ close;
%mend;
%SendEmailFAST;
CLOSE the library connection before sending the email would be my first suggestion. Use:
LIbname x clear;
after adding the data but before calling the email macro.
The Libname means that SAS is still using the file and is very likely to cause problems like this.
Note: post code and log entries into a text box opened with the forum's </> icon to improve readability.
@Ashpak wrote:
Hi All
I have created below code to export my SAS data set into xlsx file with multiple sheets, file got created and sent email with attachment but xls file unable to open , its says file is corrupted, if we manually transfer xlsx file from SAS server to windows Desktop through WINSCP, file gets opened , but its not opening through email and SAS server locations. here is the code. Appreciate your help on this
libname x xlsx "/workspace/lob5/alm/u441070/xyz.xlsx";
data x.DQ_Pricing_Completeness;
set work.pdq001;
run;
data x.DQ_Pricing_Integrity;
set work.pdq002;
run;
%macro SendEmailFAST;
filename outbox email
to=(&emailTo1)
from=(&emailFrom)
cc=(&emailCC)
bcc=(&emailbcc)
type='text/html'
subject= "Data Quality Run &scn -&dbn &taday"
attach="/workspace/lob5/alm/u441070/xyz.xlsx" ;
ODS html body=outbox style=Harvest; /*BarrettsBlue*/
/* Pricing*/
Title1 color=red H=3 "Data Quality Exceptions Report NVP Pricing Templates for &scn%";
proc report data=Email_Fast_Rep_Pricing ;
columns CNT DQ_Dimension DQRule Field DQCHEK_POINT DQPriority;
define DQPriority / display; /* explicitly specify column usage */
define DQCHEK_POINT / display;
compute DQPriority;
if DQPriority='High' then call define(_col_, "style", "style=[backgroundcolor=Red]");
if DQPriority='Medium' then call define(_col_, "style", "style=[backgroundcolor=coral]");
if DQPriority='Low' then call define(_col_, "style", "style=[backgroundcolor=cyan]");
if DQPriority='No Actions' then call define(_col_, "style", "style=[backgroundcolor=Green]");
endcomp;
compute DQCHEK_POINT;
if DQCHEK_POINT='Fail' then call define (_col_, 'style', 'style=[foreground=Red]');
endcomp;
run;
Title1;
ods _all_ close;
%mend;
%SendEmailFAST;
Can you open the file before sending the email?
Try modifying your FILENAME statement to ensure your attachment is recognised as an XLSX-type spreadsheet.
filename outbox email
to=(&emailTo1)
from=(&emailFrom)
cc=(&emailCC)
bcc=(&emailbcc)
type='text/html'
subject= "Data Quality Run &scn -&dbn &taday"
(attach="/workspace/lob5/alm/u441070/xyz.xlsx" content_type="application/xlsx");
I've sent in the past XLSX email attachments created in a Linux environment and never had such problems.
You are using your SAS server to send the email so your EG version is irrelevant. SAS version could be relevant though, so post the results of this:
%put &SYSVLONG;
I suggest that you try this test - upload a known good XLSX spreadsheet to your SAS server. Then email it to yourself using your SAS program. Does it open OK? If so there is nothing wrong with the SAS email attachment process. There must be something wrong with the original spreadsheet you are trying to attach.
Did you already try what @SASKiwi proposes. That looks promising as it tells the email server that the attachment is of type XLSX so the email server won't do "bad things" to it.
Corrupted files cannot be opened. Something is corrupting it in the e-mail process. This really isn't a SAS issue at all.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: