Exported XLSX file unable to open corrupted

[Ashpak]

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;

[ballardw]

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;

---

[Ashpak]

Still the problem same unable to open even after closing libname before calling to email macros.

LIbname x clear;

[ballardw]

Can you open the file before sending the email?

[Ashpak]

Since file is written in Unix environment location if we open the file in binary formats

[SASKiwi]

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

[Ashpak]

Yes tried still the same issues unable to open xlsx (attach="/workspace/lob5/alm/u441070/xyz.xlsx" content_type="application/xlsx");

Hence thinking to use Text file with Tab delimited options if xlsx is not working

[Patrick]

I've sent in the past XLSX email attachments created in a Linux environment and never had such problems.

[Ashpak]

Is this problem with Sas enterprise guide version , I’m using sas EG7.1 version

[SASKiwi]

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. 

[Patrick]

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.

[PaigeMiller]

Corrupted files cannot be opened. Something is corrupting it in the e-mail process. This really isn't a SAS issue at all.