fill an Excel Template

[Rakeon]

Hi to all,

I have a Template in Excel, and I would like to fill the Excel's cell from SAS data set...

what should I do?

which approach do you suggest to me?

Thanks!

[Cynthia_sas]

Hi: There are a couple of approaches that my students have described:

1) write a CSV file with SAS to populate the Excel template and have the template get the CSV file as input

2) use DDE to populate the template cells based on the SAS data

3) depending on what your template is doing, redesign your process and have the output created by ODS -- for example, if your template is only performing cosmetics or sums, do those things in SAS/ODS using style overrides and procedure options and then allow ODS to create the output file without using an Excel template.

  #1 and #2 approaches still have you with one foot in the Excel world and 1 foot in the SAS world for file creation/maintenance. #3 approach has you in the SAS world entirely for creation and in the Excel world for viewing.

cynthia

[raindrops]

DDE Approach: This will write data from ‘sasfile’ called in a template called ‘report’, starting at row 10 column 3 or the template to row 324 column 11. Write variables “height (weight name age class Injury)” and save as “final_report” in same location as template. ____________ options noxwait noxsync; x "'template path\report.xlsx'"; data _null_; x=sleep(1); run; filename report dde "excel|templatepath\[report.xlsx]new ICD10!r10c3:r324c11" notab recfm=v LRECL=500; data _null_; set sasfile; file report ; put height (weight name age class Injury) ('09'x); run; data _null_; x=sleep(1); run; filename cmp dde 'Excel|System'; data _null_; file cmp; put %unquote(%str(%'[SAVE.as("template path\final_Report.xlsx")]%')); put '[QUIT()]'; run;

[art297]

Download the free macro from: A Poor/Rich SAS Users Proc Export - sasCommunity

How you would use the macro depends upon how complicated the task is. E.g., the following code would copy sashelp.class names, sex and height, using a template called template, to new workbook called "class_stats", create a sheet called Sept_2014, and starting the data at a range beginning at cell a2:

%exportxl(data=sashelp.class (keep=name sex height),

template=c:\art\Template.xltx,

templatesheet=Template,

outfile=c:\art\class_stats.xlsx,

sheet=Sept_2014,

type=N,

usenames=no,

range=A2)

Similarly, the following code would modify the existing sheet, adding Weight to a range beginning at cell I2:

%exportxl(data=sashelp.class (keep=weight),

outfile=c:\art\class_stats.xlsx,

sheet=Sept_2014,

type=M,

usenames=no,

range=I2)

[art297]

Note: After noticing an error in the code I just updated the exportxl macro. Like before, the macro (and paper) can be downloaded at: A Poor/Rich SAS Users Proc Export - sasCommunity

[raindrops]

I tried this macro and it didn't work.

I doesn't run at all?

Any ideas?

[art297]

Some questions:

What operating system are you using?

Are you running SAS on your computer or via a server?

Did you run (compile) the macro before calling it?

What code did you submit to run the macro?

What did your log say?

[Reeza]

Another simple method is to create a extra tab in your worksheet and include the data there in a named range. Link the formatted areas of the excel sheet to the named range data.

Then export to the named range using a standard export procedure and the data will be updated.

[DanD]

Reeza wrote:

Another simple method is to create a extra tab in your worksheet and include the data there in a named range. Link the formatted areas of the excel sheet to the named range data.

Then export to the named range using a standard export procedure and the data will be updated.

Reeza,

What do you mean by "standard export procedure"? Is that a proc export? If so, how do I specify the named range? Thanks.

[Reeza]

Here's a link to a presentation I did on the topic which includes some sample code.  The presentation isn't really standalone, but should get you started. Make sure to review the drawbacks slide - this won't work in all cases.

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhu...

[DanD]

Reeza wrote:

Here's a link to a presentation I did on the topic which includes some sample code.  The presentation isn't really standalone, but should get you started. Make sure to review the drawbacks slide - this won't work in all cases.

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhu...

I was able to do what I wanted using proc export like this:

/*    export the data for the provider to a spreadsheet */

proc export data=&provider

file="&prgdata./&provider._test.xlsx"

dbms=xlsx replace;

sheet='Template';

      run;

  I think this is new in 9.4. I will look at your presentation, though. I'm sure I'll learn something new. Thanks for the reply.