Best way to put data from sas into pre-formatted Excel xslx file automatically

[ChrisNZ]

This post easily earns the price for the tersest question!

Using a libname statement with the xlsx engine allows you to push data into an existing file.

%* Create sample excel file;
proc export data=SASHELP.CLASS(obs=2) dbms=xlsx outfile="%sysfunc(pathname(WORK))\class.xlsx" replace;run;

%* Print contents;
libname XL xlsx "%sysfunc(pathname(WORK))\class.xlsx";
proc print data=XL.CLASS; run;

%* Update and print contents;
data XL.CLASS; set SASHELP.CLASS(obs=3); run;
proc print data=XL.CLASS; run;

Obs	Name	Sex	Age	Height	Weight
1	Alfred	M	14	69	112.5
2	Alice	F	13	56.5	84

---

Obs	Name	Sex	Age	Height	Weight
1	Alfred	M	14	69	112.5
2	Alice	F	13	56.5	84
3	Barbara	F	13	65.3	98

[SASKiwi]

One approach that works well is to use the SAS Add-in to MS Office. You set a data sheet that is simply a copy of a SAS dataset. When you want to update it you just use the SAS tab on the Excel menu and do a Data Refresh.

The next step is you run an Excel macro (I'm assuming competence in this area, something I lack) which reads the data sheet and then populates your pre-formatted sheet exactly how you want it.

The advantage of this method is it is simple, the disadvantage is it requires manual steps.