Desktop productivity for business analysts and programmers

UNIX copy Excel template command not copying pre-created template tabs

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

UNIX copy Excel template command not copying pre-created template tabs

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

 


Accepted Solutions
Solution
‎07-20-2017 09:09 AM
Super User
Posts: 7,760

Re: UNIX copy Excel template command not copying pre-created template tabs

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_.

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

View solution in original post


All Replies
Solution
‎07-20-2017 09:09 AM
Super User
Posts: 7,760

Re: UNIX copy Excel template command not copying pre-created template tabs

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_.

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

Re: UNIX copy Excel template command not copying pre-created template tabs

Posted in reply to KurtBremser

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

 

Super User
Posts: 7,760

Re: UNIX copy Excel template command not copying pre-created template tabs

&reportexcel already has &appdir in it, so don't use &appdir./ in the outfile.

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

Re: UNIX copy Excel template command not copying pre-created template tabs

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;

Super User
Posts: 7,760

Re: UNIX copy Excel template command not copying pre-created template tabs

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.

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

Re: UNIX copy Excel template command not copying pre-created template tabs

Posted in reply to KurtBremser

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? 

Super User
Posts: 7,760

Re: UNIX copy Excel template command not copying pre-created template tabs

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.

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

Re: UNIX copy Excel template command not copying pre-created template tabs

Posted in reply to KurtBremser

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.

 

Super User
Posts: 7,760

Re: UNIX copy Excel template command not copying pre-created template tabs

It may be that your template file is opened by Excel while you copy it: http://blogs.sas.com/content/sasdummy/2014/09/24/proc-import-xlsx-error/

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

Re: UNIX copy Excel template command not copying pre-created template tabs

Posted in reply to KurtBremser

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!

Super User
Posts: 7,760

Re: UNIX copy Excel template command not copying pre-created template tabs

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.

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

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

Discussion stats
  • 11 replies
  • 259 views
  • 1 like
  • 2 in conversation