Excel export with format

[TBS1]

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,

[Reeza]

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]

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]

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]

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

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

[TBS1]

one possible solution is through RWI. RWI is similar to html. Maybe this hint will help others.

[Patrick]

@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]

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.