BookmarkSubscribeRSS Feed
Rakeon
Quartz | Level 8

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!

10 REPLIES 10
Cynthia_sas
SAS Super FREQ

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
Calcite | Level 5
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
Opal | Level 21

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
Opal | Level 21

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
Calcite | Level 5

I tried this macro and it didn't work.

I doesn't run at all?

Any ideas?

art297
Opal | Level 21

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 19196 views
  • 11 likes
  • 6 in conversation