How to export from sas to excel using a template

Question

proc export data=data1
    OUTFILE="place_on_pc\template_SASdata.xlsx"
    DBMS=EXCEL REPLACE;
    sheet=input;  
run;

Simple proc export code in SAS. However, I want to use the the "template_SASdata.xlsx" file as an template and save my data on a brand new file with the same sheets as my original excel template but updated 'input' sheet.

input="place_on_pc\template_SASdata.xlsx"
output="place_on_pc\data1_SASdata.xlsx"

How do I include that in my proc export? I have tried to add

infile = ="place_on_pc\template_SASdata.xlsx"

in my proc export but that doesn't work.

Joe Joe · Accepted Answer · 2016-12-06T14:34:36

You'd need to copy the original file to the new file first, and then export to replace the sheet you're adding data to.

Alternately, a method I have used and prefer, you can export to a file not related to the template, and then have a VBA macro in the template that you run that you identify the data sheet and the macro opens the data sheet, does whatever with the data, and saves a new file at the end. I like this if you're going to be doing any other formatting as excel has fairly good tools for what you're doing here. If you're not doing any other formatting, then have SAS copy the file (assuming you are able to use x commands in your environment).

x 'copy [template file] [new file]'

How to export from sas to excel using a template

1 Answers