Desktop productivity for business analysts and programmers

Emailing Excel attachment SAS EG5.1 Error

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Emailing Excel attachment SAS EG5.1 Error

Hi guys,

 

I have been trying to export an excel file in a SAS project where I had generated the output. However, an error prompted out saying that the file is in use. Any idea how to solve this error as the file was not being opened by me when I am running the project. Thanks very much. 

 

Below shows  the coding which I am using.

 

%macro data_check;

data _null_;

trading_dt=today()-2;

filedate = put(trading_dt, yymmddn8.);

put trading_dt date9.;

put filedate;

 

call symput('trading_dt', trading_dt);

call symput('filedate', filedate);

run;

proc sql ;

select count(*) into :cnt

from WORK.WET_REP_TXN ;

quit;

%if &cnt ne 0 %then %do;

ODS html file='\\10.64.23.39\Users\88094701\WET_REP_txn.xls' STYLE=minimal;

proc print data=WET_REP_TXN noobs;

run;

ods tagsets.excelxp close;

FILENAME alert EMAIL from=("to_me@hotmail.com")

to=("to_me@hotmail.com")

Subject = "Generated - storage.customer &filedate "

attach=("\\10.64.23.39\Users\88094701\WET_REP_txn.xls" content_type="application/vnd.ms-excel");

data _null_;

file alert;

put "storage.customer is ready";

run;

%end;

 

%mend;

%data_check;

 

 

 

Attachment

Accepted Solutions
Solution
‎02-10-2017 08:39 AM
Esteemed Advisor
Posts: 6,646

Re: Emailing Excel attachment SAS EG5.1 Error

This is the source of your problem:

ODS html file='\\10.64.23.39\Users\88094701\WET_REP_txn.xls' STYLE=minimal ;

......


ods tagsets.excelxp close;

You open one ODS destination, but close another, so the xls file is kept open.

An it is EXTREMELY bad style to use ods html with an xls filename extension. If you create HTML, use .html as extension. If, OTOH, you use tagsets.excelxp, use .xml as the extension, as that aligns with the content.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎02-10-2017 08:39 AM
Esteemed Advisor
Posts: 6,646

Re: Emailing Excel attachment SAS EG5.1 Error

This is the source of your problem:

ODS html file='\\10.64.23.39\Users\88094701\WET_REP_txn.xls' STYLE=minimal ;

......


ods tagsets.excelxp close;

You open one ODS destination, but close another, so the xls file is kept open.

An it is EXTREMELY bad style to use ods html with an xls filename extension. If you create HTML, use .html as extension. If, OTOH, you use tagsets.excelxp, use .xml as the extension, as that aligns with the content.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Emailing Excel attachment SAS EG5.1 Error

Hi Kurt, thanks a lot for the help.
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Emailing Excel attachment SAS EG5.1 Error

Just to add to @KurtBremser there, do you think email is the best medium for sending what could be sensitive and possibly large datafiles?  If they are reports (and you wouldn't be using a bad data medium to convey data would you), then have some sort of secure reporting environment where you reports are published for authorised users to login and view, you can then send them an email when there is new files.  If you are doing a data transfer, then use a good data medium, CSV/XML etc., and use a secure portal to upload data to, then they can log in and download the data securely.

Note, your email client will likely have size of email restrictions in place that will prevent you frm sending large files.

Occasional Contributor
Posts: 5

Re: Emailing Excel attachment SAS EG5.1 Error

Hi there mate, I would definitely say that sending outputs through emails aren't the best and recommended way because of the security and possibly large files issue. However, the output which I am working on has no output most of the day (daily run) and maximum of 5 outputs daily.

Out of the topic question, do you know any SAS EG course which a beginner can attend so that they would be able to write a coding similar to the above?
Trusted Advisor
Posts: 1,052

Re: Emailing Excel attachment SAS EG5.1 Error

SAS EG training tends to cover how to use the tool, mainly the point-and-click tasks and query builder.

 

To cover SAS coding you're better off looking at the "SAS Programming" series of courses. I believe that at least the first one is free on the SAS website.

 

Tom

Trusted Advisor
Posts: 1,052

Re: Emailing Excel attachment SAS EG5.1 Error

Alternately, I'd be happy to work with and tutor your folks, for a small fee!  ;-)

Esteemed Advisor
Posts: 6,646

Re: Emailing Excel attachment SAS EG5.1 Error

One thing that helps greatly on the path to better coding is adapting a solid, consistent, visual coding style that makes code easier to read and identify logical units.

Consider these slight changes to your original code:

%macro data_check;

data _null_;
  trading_dt = today() - 2;
  filedate = put(trading_dt,yymmddn8.);
  put trading_dt date9.;
  put filedate;
  call symput('trading_dt',trading_dt);
  call symput('filedate',filedate);
run;

proc sql noprint;
  select
    count(*) into :cnt
    from WORK.WET_REP_TXN
  ;
quit;

%if &cnt ne 0 %then %do;

ods tagsets.excelxp file='\\10.64.23.39\Users\88094701\WET_REP_txn.xml' style=minimal;

proc print data=WET_REP_TXN noobs;
run;

ods tagsets.excelxp close;

filename alert email
  from=("to_me@hotmail.com")
  to=("to_me@hotmail.com")
  subject = "Generated - storage.customer &filedate "
  attach=("\\10.64.23.39\Users\88094701\WET_REP_txn.xml" content_type="application/vnd.ms-excel")
;

data _null_;
  file alert;
  put "storage.customer is ready";
run;

%end;

%mend;

%data_check;

The details of such coding style are up to you (how many columns used for each indentation etc), but visually pleasing code makes for faster working and easier detection of logical/semantic mistakes.

Keep in mind that someone has to make sense of your code someday, and most of the time that someone will be you, so be good to your future self!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 164 views
  • 1 like
  • 4 in conversation