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

I'm working on an Excel template with multiple data tabs, most of which feed data into two presentation tabs, and a SAS EG (not PC SAS) program to copy output data sets into those data tabs...but the copy command isn't opying the presentation tabs in the template before the proc export commands start replacing the data tabs.  So after the program runs, I get an Excel file with all the data sets copied into their own tabs, but no presentation tabs copied over from the template.  Any thoughts on how to correct this?

 

Original SAS Code:

 

%let RunDate = %sysfunc(TODAY(), mmddyyd10.);

%let AppDir=/n04/data/sasug169/Errors;

%let ReportType=Steering_Comm_Claims;

%let TemplateExcel=&AppDir./&ReportType._template.xlsx;

%let ThisReport=Steering_Comm_Claims (as of &RunDate);

%let ReportExcel=&AppDir./&ReportType._&ThisReport..xlsx;

%let UnixCmd=cp &TemplateExcel &ReportExcel;

filename UnixPipe pipe "&UnixCmd" ;

data _null_;

infile UnixPipe pad missover lrecl=400;

input UnixReply $400.;

run;

put UnixReply;

filename UnixPipe clear;

 

/*EXPORT WORKS, BUT DOESN'T RETAIN PRESENTATION TAB*/

 

PROC EXPORT

DATA=WORK.STEERING_DATA_1

dbms=xlsx

OUTFILE="&AppDir/&ThisReport.xlsx"

replace;

SHEET="STEERING_DATA_1";

putnames=yes;

RUN;

 

 

SAS Log Notes:

 

NOTE: The infile UNIXPIPE is:

 

Pipe command="cp /n04/data/sasug169/RPM_Ops_Claims_Errors/Steering_Comm_Claims_template.xlsx

/n04/data/sasug169/RPM_Ops_Claims_Errors/Steering_Comm_Claims_Steering_Comm_Claims (as of 07-18-2017).xlsx"

NOTE: 2 records were read from the infile UNIXPIPE.

The minimum record length was 61.

The maximum record length was 210.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

 

1 ACCEPTED SOLUTION

Accepted Solutions
11 REPLIES 11
Kurt_Bremser
Super User

You should use &ReportExcel as outfile of the proc export, because that is the copy target of the cp.

And put the put statement before the run; that ends the data _null_.

PatrickB
Calcite | Level 5

Thank you for that - I was just reviewing it & wondering why I was doing that.  I changed the outfile and the location of the run, but it's now working wrose that before, and not creating the file at all.

 

%let RunDate = %sysfunc(TODAY(), mmddyyd10.);

%let AppDir=/n04/data/sasug169/RPM_Ops_Claims_Errors;

%let ReportType=Steering_Comm_Claims;

%let TemplateExcel=&AppDir./&ReportType._template.xlsx;

%let ThisReport=Steering_Comm_Claims (as of &RunDate);

%let ReportExcel=&AppDir./&ReportType._&ThisReport..xlsx;

%let UnixCmd=cp &TemplateExcel &ReportExcel;

filename UnixPipe pipe "&UnixCmd" ;

data _null_;

infile UnixPipe pad missover lrecl=400;

input UnixReply $400.;

put UnixReply;

filename UnixPipe clear;

run;

 

/*EXPORT WORKS, BUT DOESN'T RETAIN PRESENTATION TAB*/

 

PROC EXPORT

DATA=WORK.STEERING_DATA_1

dbms=xlsx

OUTFILE="&AppDir/&ReportExcel.xlsx"

replace;

SHEET="STEERING_DATA_1";

putnames=yes;

RUN;

 

 

SAS Output Log:

 

494 filename UnixPipe clear;

NOTE: Fileref UNIXPIPE has been deassigned.

495 run;

ERROR: No logical assign for filename UNIXPIPE.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

 

499 PROC EXPORT

500 DATA=WORK.STEERING_DATA_1

501 dbms=xlsx

502 OUTFILE="&AppDir/&ReportExcel.xlsx"

503 replace;

504 SHEET="STEERING_DATA_1";

505 putnames=yes;

506 RUN;

ERROR: Temporary file for XLSX file can not be created ->

/n04/data/sasug169/RPM_Ops_Claims_Errors//n04/data/sasug169/RPM_Ops_Claims_Errors//Steering_Comm_Claims_Steering_Comm_Claims (as of

07-18-2017).$$1. Make sure the path name is correct and that you have write permission.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

 

507

 

PatrickB
Calcite | Level 5

Update:

 

So I changed the OUTFILE="&AppDir/&ReportExcel.xlsx" to

OUTFILE="&ReportExcel."

and it's processing through to the end, and delivering a file copy of the template, but stil without the presentation tabs copying over

 

 

 

 

 

 

 

%let RunDate = %sysfunc(TODAY(), mmddyyd10.);

%let AppDir=/n04/data/sasug169/RPM_Ops_Claims_Errors;

%let ReportType=Steering_Comm_Claims;

%let TemplateExcel=&AppDir./&ReportType._template.xlsx;

%let ThisReport=Steering_Comm_Claims (as of &RunDate);

%let ReportExcel=&AppDir./&ReportType._&ThisReport..xlsx;

%let UnixCmd=cp &TemplateExcel &ReportExcel;

filename UnixPipe pipe "&UnixCmd" ;

data _null_;

infile UnixPipe pad missover lrecl=400;

input UnixReply $400.;

put UnixReply;

run;

filename UnixPipe clear;

 

/*EXPORT WORKS, BUT DOESN'T RETAIN PRESENTATION TAB*/

 

PROC EXPORT

DATA=WORK.STEERING_DATA_1

dbms=xlsx

OUTFILE="&ReportExcel."

replace;

SHEET="STEERING_DATA_1";

putnames=yes;

RUN;

Kurt_Bremser
Super User

Omit the replace option from the proc export statement. If SAS complains about the existing sheet, make sure that the template file does not yet have the sheet you want to create.

That's what I read from the documentation.

PatrickB
Calcite | Level 5

I first removed the "replace;" lines from the code, but the program failed:

ERROR 22-322: Syntax error, expecting one of the following: ;, DATA, DBLABEL, DBMS, DEBUG, FILE, LABEL, OUTFILE, OUTTABLE, REPLACE,

TABLE, _DEBUG_.

ERROR 76-322: Syntax error, statement will be ignored.


Removing the data tabs from the template breaks the links to the presentation tabs (resulting in the #REF errors, which wouldn't be corrected when the tabs are added through the SAS program), which then defeats the whole purpose of the process.  I guess what it comes down to is, can SAS/UNIX actually copy an Excel file completely or not?  Because that is the benefit of this exercise.  What are your thoughts on the file copying functionality? 

Kurt_Bremser
Super User

replace is an option of the proc export statement, so you must keep the semicolon.

Then try again with a template file that has all tabs, and see if you can rewrite just one tab.

A UNIX cp that exits without a message has done its job, byte-by-byte, period.

PatrickB
Calcite | Level 5

I made some small tweaks to the code, and now the template is being copied in its entirety into a new file with a date stamp, but now the export/replace to the data tabs are not:

 

 

%let RunDate = %sysfunc(TODAY(), mmddyyd10.);

%let AppDir=/n04/data/sasug169/RPM_Ops_Claims_Errors;

%let ReportType=Steering_Comm_Claims;

%let TemplateExcel=&AppDir./&ReportType._template.xlsx;

%let ThisReport=_as_of_&RunDate;

%let ReportExcel=&AppDir./&ReportType._&ThisReport..xlsx;

%let UnixCmd=cp &TemplateExcel &ReportExcel;

filename UnixPipe pipe "&UnixCmd" ;

data _null_;

infile UnixPipe pad missover lrecl=400;

input UnixReply $400.;

put UnixReply;

run;

filename UnixPipe clear;

 

/*TEMPLATE COPY WORKS, BUT NOW EXPORT/REPLACE DOES NOT*/

 

PROC EXPORT

DATA=WORK.STEERING_DATA_1

dbms=xlsx

OUTFILE="&ReportExcel."

REPLACE;

SHEET="STEERING_DATA_1";

putnames=yes;

RUN;

 

 

Error creating XLSX file -> /n04/data/sasug169/RPM_Ops_Claims_Errors//Steering_Comm_Claims__as_of_07-19-2017.xlsx . It is either not an Excel spreadsheet or it is damaged. Error code=8014900A

Requested Output File is Invalid

ERROR: Export unsuccessful. See SAS Log for details.

 

PatrickB
Calcite | Level 5

I've already researched the error code and found those blog pages - that didn't appear to be the issue at hand, as I was closing out of the template for a few moments/minutes before running the code, but sure enough, I ran the code this morning without starting up Excel first, and the code ran to completion with no errors.  Any idea how long the Excel file lock lasts?  Outside of that, I believe that the issue is solved, and I'll mark a solution later today - thanks for all the guidance!

Kurt_Bremser
Super User

Welcome to the world of Micro-Crap.

Since CP/M (the ancestor of DOS and therefore Windows) was never meant for multitasking, it did not have file-locking implemented. So a lot of applications worked around that defect by writing the file-locks to the files themselves (a horrible concept, but that's what you get when a law-school dropout tries his hand at programming), and that includes MS Office.

 

Those products are very clever random number generators in terms of function. Now they work, now they don't.

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
  • 11 replies
  • 1204 views
  • 1 like
  • 2 in conversation