Help using Base SAS procedures

Copying and renaming a XLSX file using SAS 9.3

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Copying and renaming a XLSX file using SAS 9.3

Hello all,

 

I use the following program to export a SAS dataset to an XLSX template workbook:

 

proc export 
data=work.dataset
file="/directory/Workbook Template"
dbms=XLSX
replace
label;
sheet='Data';

However, the next step I would like is for the the workbook to be copied (so I keep the original template) and rename the workbook just created with something like "Workbook &SYSDATE.xslx"

 

I have tried the solution to this post with no success and also tried the following code:

 

data _null_;
infile "/directory/Workbook Template.xlsx" lrecl=32767;
file "/directory/Workbook &RPT_DT_CHAR..xlsx";
input;
put _infile_;
run;

Yet this corrupts the workbook and renders it unopenable.

 

Does anyone have a solution to this problem that they could share with me please?

 

Regards,

 

Ed


Accepted Solutions
Solution
‎10-18-2016 10:11 AM
Super User
Super User
Posts: 7,429

Re: Copying and renaming a XLSX file using SAS 9.3

An XLSX file is actually a ZIP file with lots of contents.  Hence reading/writing it as character will result in a corrupt file.  You need to read/write as byte per Chris's post here:

http://blogs.sas.com/content/sasdummy/2011/06/17/how-to-use-sas-data-step-to-copy-a-file-from-anywhe...

 

To use datastep method.

 

As @Reeza has said you could use OS commands via X if that is enabled.  Alternatively just export the file twice.

View solution in original post


All Replies
Super User
Posts: 17,928

Re: Copying and renaming a XLSX file using SAS 9.3

Try an X command statement

 

x 'copy "path to file1" "path to new file"';

 

Solution
‎10-18-2016 10:11 AM
Super User
Super User
Posts: 7,429

Re: Copying and renaming a XLSX file using SAS 9.3

An XLSX file is actually a ZIP file with lots of contents.  Hence reading/writing it as character will result in a corrupt file.  You need to read/write as byte per Chris's post here:

http://blogs.sas.com/content/sasdummy/2011/06/17/how-to-use-sas-data-step-to-copy-a-file-from-anywhe...

 

To use datastep method.

 

As @Reeza has said you could use OS commands via X if that is enabled.  Alternatively just export the file twice.

Occasional Contributor
Posts: 10

Re: Copying and renaming a XLSX file using SAS 9.3

Cheers to you both, though Chris's method in RW9's post worked the best for me.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 256 views
  • 0 likes
  • 3 in conversation