topic Copying and renaming a XLSX file using SAS 9.3 in SAS Procedures

Copying and renaming a XLSX file using SAS 9.3

edmb — Tue, 18 Oct 2016 08:39:10 GMT

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,

Re: Copying and renaming a XLSX file using SAS 9.3

Reeza — Tue, 18 Oct 2016 09:25:16 GMT

Try an X command statement

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

Re: Copying and renaming a XLSX file using SAS 9.3

RW9 — Tue, 18 Oct 2016 09:40:40 GMT

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-anywhere/

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.

Re: Copying and renaming a XLSX file using SAS 9.3

edmb — Tue, 18 Oct 2016 14:10:50 GMT

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