BookmarkSubscribeRSS Feed
Ashpak
Calcite | Level 5

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;

11 REPLIES 11
ballardw
Super User

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
Calcite | Level 5
Still the problem same unable to open even after closing libname before calling to email macros.

LIbname x clear;
ballardw
Super User

Can you open the file before sending the email?

Ashpak
Calcite | Level 5
Since file is written in Unix environment location if we open the file in binary formats
SASKiwi
PROC Star

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
Calcite | Level 5
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
Opal | Level 21

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

Ashpak
Calcite | Level 5
Is this problem with Sas enterprise guide version , I’m using sas EG7.1 version
SASKiwi
PROC Star

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
Opal | Level 21

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

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

 

--
Paige Miller

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 11 replies
  • 1491 views
  • 1 like
  • 5 in conversation