BookmarkSubscribeRSS Feed
TBS1
Fluorite | Level 6

Hello all,

I have the following task and would like to discuss my solution approach with you:

In my SAS application, I have data that I want to export to an excel document.

It is important that the data is written to the excel file in a certain format.

I have an excel document that has a table structure template at the very top of a worksheet. In this table structure, variable names are given, such as Name1.

I want to replace these with the variable value of my SAS variable (e.g. mySASVariable1) from my SAS application.

 

This table structure should then be repeated for each SAS observation in the excel document: The whole thing reminds me of the mail merge function from Word, where you access the data from an excel table.

 

My idea was now the following:


1. read in the excel document with the template table structure.
2. loop through my SAS dataset.
3. replace the Excel variables with the values from the SAS application.
4. write each table structure replaced with SAS variables into the previously opened Excel document.

 

I hope this will preserve the table structure and formatting.

Is this "a good way to do", or would you take a different approach?

 

Thanks in advance,

7 REPLIES 7
Reeza
Super User
How complex is your table structure? SAS datasets don't really mimic complex Excel structures. Can you make a small reproducible example that illustrates your problem? Fake data is fine.
Tom
Super User Tom
Super User

Doesn't sound worth the effort. 

Either generate the whole report using SAS. 

 

Or export just the data and use Excel coding to copy the values from the export into the places you want it to appear.  For example by having a DATA sheet in the WORKBOOK that you can update and then where you want the pretty text to appear just reference to the cell in report.

 

Note that SAS used to have a nice mail merge type tool (PROC FSLETTER).  But that was back in the days of line printers.

ballardw
Super User

Another vote for make the entire report in SAS and then export to Excel, if at all practical. No example provided so can't judge feasibility.

 

I had to maintain some "Excel Templated" reports for awhile and even after using them for 6+ months kept finding buried "gotcha's", as in one formula in a cell that was not documented. When I had to add a report element that was such a headache I hated every data refresh as something would break such as the first time a value went to zero or some other threshold.

 

BTW there is tool called the Report Writing Interface (RWI) in the data step that will let you do all sorts of odd table structures. Like any flexible tool there is a learning curve associated but that might be an option if Proc Report/ Tabulate/ Print can't quite generate an appearance you want.

 

TBS1
Fluorite | Level 6

Thank you so far for your comments! An example for the template is in this picture:template.png

 

After cell A32, the area of A1:I32 should be repeated.

TBS1
Fluorite | Level 6
one possible solution is through RWI. RWI is similar to html. Maybe this hint will help others.
Patrick
Opal | Level 21

@TBS1 

SAS can't update existing Excel sheets but can only create/replace them in full.

If you don't need to stick 100% to the layout then the least effort approach would be to use a SAS Proc like Proc Report together with ODS EXCEL to create the full report with SAS.

If the layout is not negotiable or if you have an Excel template with multiple sheets that you need to populate at different times and with different programs then write your data to a data sheet and use cell references and formulas in your report sheet to read this data into your report. That's how I've seen working for such cases.

 

If you really must update an existing Excel sheet directly then Python library openpyxl provides such functionality. I would avoid going down this path though as you would likely end-up in a lot of tedious coding with cell references.

 

SASKiwi
Opal | Level 21

To me this looks like you doing a custom report that ignores Excel's tabular structure. IMHO you would be better off writing this report using a SAS DATA step, and outputting this to a PDF document.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 7 replies
  • 259 views
  • 0 likes
  • 6 in conversation