topic Re: fill an Excel Template in ODS and Base Reporting

fill an Excel Template

Rakeon — Thu, 04 Sep 2014 20:14:13 GMT

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!

Re: fill an Excel Template

Cynthia_sas — Thu, 04 Sep 2014 23:37:01 GMT

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

Re: fill an Excel Template

art297 — Fri, 05 Sep 2014 00:58:45 GMT

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)

Re: fill an Excel Template

art297 — Sat, 06 Sep 2014 20:09:53 GMT

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

Re: fill an Excel Template

Reeza — Sun, 07 Sep 2014 01:10:04 GMT

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.

Re: fill an Excel Template

DanD — Thu, 09 Apr 2015 04:30:41 GMT

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.

Re: fill an Excel Template

Reeza — Thu, 09 Apr 2015 15:30:49 GMT

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/FareezaKhurshed-CoordinateComplexReports-Spring2014.pdf

Re: fill an Excel Template

DanD — Thu, 09 Apr 2015 17:58:06 GMT

Reeza wrote:

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhurshed-CoordinateComplexReports-Spring2014.pdf

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.

Re: fill an Excel Template

raindrops — Wed, 14 Nov 2018 14:36:45 GMT

I tried this macro and it didn't work.

I doesn't run at all?

Any ideas?

Re: fill an Excel Template

raindrops — Fri, 16 Nov 2018 14:07:40 GMT

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;

Re: fill an Excel Template

art297 — Fri, 16 Nov 2018 14:38:43 GMT

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?