topic Re: Emailing Excel attachment SAS EG5.1 Error in SAS Enterprise Guide

Emailing Excel attachment SAS EG5.1 Error

vannesslee — Fri, 10 Feb 2017 13:20:29 GMT

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;

Re: Emailing Excel attachment SAS EG5.1 Error

Kurt_Bremser — Fri, 10 Feb 2017 13:27:39 GMT

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.

Re: Emailing Excel attachment SAS EG5.1 Error

RW9 — Fri, 10 Feb 2017 13:32:31 GMT

Just to add to @Kurt_Bremser 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.

Re: Emailing Excel attachment SAS EG5.1 Error

vannesslee — Fri, 10 Feb 2017 13:43:09 GMT

Hi Kurt, thanks a lot for the help.

Re: Emailing Excel attachment SAS EG5.1 Error

vannesslee — Fri, 10 Feb 2017 13:48:39 GMT

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?

Re: Emailing Excel attachment SAS EG5.1 Error

TomKari — Fri, 10 Feb 2017 14:41:43 GMT

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

Re: Emailing Excel attachment SAS EG5.1 Error

TomKari — Fri, 10 Feb 2017 14:42:28 GMT

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

Re: Emailing Excel attachment SAS EG5.1 Error

Kurt_Bremser — Fri, 10 Feb 2017 15:01:02 GMT

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!