topic Re: SAS to Excel export: file corrupted one run, works the next in SAS Data Management

SAS to Excel export: file corrupted one run, works the next

adkilmer — Thu, 21 Sep 2017 19:19:09 GMT

I am running a program that pulls together several data sets. At the end of the macro, the data is all exported to named ranges in an Excel file, and the ranges populate several tables on different worksheets.

For the past few days, every time I run this program, the Excel file will not open. Instead I get the error in the attached screenshot. I get one warning in the SAS log, "No matching members in directory," on a PROC DATASETS statement. It seems like something's going wrong with the creating of the file, not the export.

Even stranger, if I run only the code that creates the Excel file and exports the data, the resulting Excel file populates correctly and can be opened. So what is causing the Excel file to get corrupted?

What follows is an abbreviated version of the code that creates the file and exports the data.

data _null_;
x "cp ""/sasprod/ca/sasdata/innovation/cost and use/CandU_Executive_Summary_Shell.xlsx""
""/sasprod/ca/sasdata/innovation/cost and use/Cost_and_Use_Dashboard_&client._&rundate..xlsx"" ";
run;

libname rptbase2 pcfiles
server=DT1
version=2007
path="\\grid\sasprod\ca\sasdata\innovation\cost and use\Cost_and_Use_Dashboard_&client._&rundate..xlsx";

proc datasets lib=rptbase2;
delete bands;
delete age_band;
delete readmissions;
quit;

proc sql;
create table rptbase2.bands as select * from bands;
quit;

proc sql;
create table rptbase2.readmissions as select * from readmissions;
quit;

proc sql;
create table rptbase2.age_band as select * from age_band;

quit;

Re: SAS to Excel export: file corrupted one run, works the next

Reeza — Thu, 21 Sep 2017 20:07:10 GMT

Can you include the log from this code?

Is there any possibility any of the data goes beyond the named ranges sizes?

Re: SAS to Excel export: file corrupted one run, works the next

adkilmer — Thu, 21 Sep 2017 20:11:13 GMT

The log for the relevant part of the program is attached.

Re: SAS to Excel export: file corrupted one run, works the next

Tom — Thu, 21 Sep 2017 20:19:54 GMT

You are really removing sheets from an XLSX file using SAS? Why?

Does the COPY command at the top have anything to do with this?

Are you sure that you waited for the file to finish copying before trying to modify it?

Are you sure that no one else is trying to read or modify the same file at the same time?

Re: SAS to Excel export: file corrupted one run, works the next

adkilmer — Thu, 21 Sep 2017 21:55:04 GMT

The code is not removing sheets. It is deleting the contents of the named ranges in Excel before loading data to them. I've found that if I skip this step, the program errors out, saying it can't write to the ranges. I have to do this even if there's no data in the range.

No one else is touching this file. Your comment about the copy finishing is interesting. Is there a way to 'pause' the program for a few seconds after the copy to make sure it finishes before I try to modify the contents?

Re: SAS to Excel export: file corrupted one run, works the next

ballardw — Thu, 21 Sep 2017 22:26:21 GMT

@adkilmer wrote:

No one else is touching this file. Your comment about the copy finishing is interesting. Is there a way to 'pause' the program for a few seconds after the copy to make sure it finishes before I try to modify the contents?

A data step and CALL SLeep is one way.

Data _null_;

call sleep(10);

run;

has the program wait for 10 seconds.

this might go immediately after the copy.

Re: SAS to Excel export: file corrupted one run, works the next

adkilmer — Thu, 21 Sep 2017 23:29:50 GMT

I added this exact code, and the program won't 'sleep.' It's blowing right past it like it's not there. The Excel issue is persisting.

Re: SAS to Excel export: file corrupted one run, works the next

adkilmer — Thu, 21 Sep 2017 23:38:06 GMT

Correction to the above - I did get the sleep to work by adding the units: call sleep(10,1). However the Excel issue is still persisting.

Re: SAS to Excel export: file corrupted one run, works the next

adkilmer — Mon, 02 Oct 2017 17:20:02 GMT

Hi everyone, I've apparently gotten this to work. I changed the code from:

data _null_;
    x "cp      ""/sasprod/ca/sasdata/innovation/cost and use/CandU_Executive_Summary_Shell.xlsx"" 
               ""/sasprod/ca/sasdata/innovation/cost and use/Cost_and_Use_Dashboard_&client._&rundate..xlsx"" ";
run;

To:

x cp  	"/sasprod/ca/sasdata/innovation/cost and use/CandU_Executive_Summary_Shell.xlsx"
		"/sasprod/ca/sasdata/innovation/cost and use/Cost_and_Use_Dashboard_&client._&rundate..xlsx" ;

If anyone could clue me in as to why code #2 works and code #1 doesn't, I'd greatly appreciate it.

Re: SAS to Excel export: file corrupted one run, works the next

Reeza — Mon, 02 Oct 2017 17:26:10 GMT

X commands are not supposed to be in a data step.

You can use CALL SYSTEM I believe in the data step if you want the X command to work.

RTM (unix reference but Windows will have the same concept)

http://documentation.sas.com/?docsetId=hostunx&docsetTarget=p0w7z1ah45wd1sn1kafn6uibm1mu.htm&docsetVersion=9.4&locale=en

Neither the X statement nor the %SYSEXEC macro program statement is intended for use during the execution of a DATA step. The CALL SYSTEM routine, however, can be executed within a DATA step. For an example, see CALL SYSTEM Routine: UNIX.

Here's an overview of calling system commands:

http://documentation.sas.com/?docsetId=hostunx&docsetTarget=p0w085btd5r0a4n1km4bcdpgqibt.htm&docsetVersion=9.4&locale=en