The SAS Output Delivery System and reporting techniques

fill an Excel Template

Reply
Contributor
Posts: 40

fill an Excel Template

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!

SAS Super FREQ
Posts: 8,645

Re: fill an Excel Template

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

Esteemed Advisor
Posts: 6,890

Re: fill an Excel Template

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)

Esteemed Advisor
Posts: 6,890

Re: fill an Excel Template

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

Grand Advisor
Posts: 16,348

Re: fill an Excel Template

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.

Contributor
Posts: 32

Re: fill an Excel Template

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.

Grand Advisor
Posts: 16,348

Re: fill an Excel Template

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...

Contributor
Posts: 32

Re: fill an Excel Template

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.

Post a Question
Discussion Stats
  • 7 replies
  • 4231 views
  • 9 likes
  • 5 in conversation