BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vannesslee
Calcite | Level 5

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;

 

 

 


error.png
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super User

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.

vannesslee
Calcite | Level 5
Hi Kurt, thanks a lot for the help.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

vannesslee
Calcite | Level 5
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?
TomKari
Onyx | Level 15

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

TomKari
Onyx | Level 15

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

Kurt_Bremser
Super User

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 7 replies
  • 1650 views
  • 2 likes
  • 4 in conversation