Multiple horizontal reports on same Excel sheet

[KonstantinVasil]

This problem has been previously discussed (link below), however I wanted to open the topic again to check if there are any new suggestions/solutions. 

Generate multiple(side by side) reports in singal excel sheet

I have several proc reports which I want to ultimately have exported horizontally on the same Excel sheet. I have been generating them with proc report and writing to Excel with ODS Excel, however, the option "ODS start at=" can't be changed within the same sheet.

In the solution referenced above, a work-around through R is used. Is it possible to solve this within SAS?

For example, I have been thinking for:

1. Prepare the reports in some kind of grid container with specific position of each report within the grid - then export the container.
2. Write the reports to an intermediary source and then export to Excel. This could be HTML or separate Excel sheets which are then combined in a single sheet.

Any discussion and suggestions are highly appreciated!

[RW9]

My suggestion, as with anything like this, is to first decide on what software's functionality you want to use.  SAS Really isn't the tool for creating spreadsheets of anything more than basic standard.  To use all the functionality of Excel, use Excel.  It even has its own programming language - VBA, which is simple and you can do anything with it inside the Office suite.

It should be very simple to dump out the data from SAS to CSV, thenhave some VBA code which loads the CSV data and processes it into the layout (can be done upfront with a template if you like) you have or want.

[KonstantinVasil]

In my case I am dealing with a process which originates from SAS with high degree of automation. The only part of the process not yet automated is the final results export and formatting which I am currently implementing, in order to save unnecessary repetitive clicking or coding in Excel. From there onwards any additional manipulation (apart from results export) in external environment brings unnecessary complication and error risk.

It is just a pity for all the things you can achieve with SAS and ODS Excel such as cell coloring, borders and fonts and then you reach a point at which placing the reports at a particular layout on the same sheet is not possible...

In short, I am looking for a solution within SAS.

[Reeza]

Do your tables have a fixed structure or do they change? If they always have the same number of columns and rows, the trick I use is to create an Excel template that links all the data sets to where they need to go. 

So one worksheet has the formatted values and links to source data sheets that are unformatted. 

Then I copy the template and use SAS to export the data to the SOURCE worksheet which updates the formatted worksheet automatically. 

FCOPY will copy files. 

I think PROC EXPORT or LIBNAME can be used to export to a specific range in Excel. 

---

@KonstantinVasil wrote:

In my case I am dealing with a process which originates from SAS with high degree of automation. The only part of the process not yet automated is the final results export and formatting which I am currently implementing, in order to save unnecessary repetitive clicking or coding in Excel. From there onwards any additional manipulation (apart from results export) in external environment brings unnecessary complication and error risk.

It is just a pity for all the things you can achieve with SAS and ODS Excel such as cell coloring, borders and fonts and then you reach a point at which placing the reports at a particular layout on the same sheet is not possible...

In short, I am looking for a solution within SAS.

---

[KonstantinVasil]

The tables have fixed structure and formatting. However, their formatting is also implemented in SAS before the exporting (including color coding etc.)

In principle if there is some way to achieve the logic below from SAS, it should work:

1. Export formatted reports to same sheet vertically - file1\sheet1  (already done)
2. Take range X1:Y1 from file1\sheet1 and copy it to location Z1 on sheet2 - on the same or new file
3. Take range X2:Y2 from file1\sheet1 and copy it to location Z2 on sheet2 - on the file from 2 without overwriting!
4. Continue for remaining tables

[Reeza]

Yeah, this method means the styling has to happen in Excel not SAS.

You can try the report writing interface instead. It does work with ODS Excel, but not sure how it will implement the styles.

[Reeza]

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-EXCEL-AND-REPORT-WRITING-INTERFACE/td-p/28...

[RW9]

Ah, then that is easy.  Output your reports to HTML or PDF, and provide them via a web front end.  I.e. drop the Excel part totally.  Or otherwise, invest in a reporting suite such as spotfire, tablueax, or one of those and provide the data that way.  Either way ditch Excel and your will find life much easier.

[KonstantinVasil]

Redirecting the output to PDF brings other problems such as: rotated spanned rows using tagattr="rotate:90" not working...

[Cynthia_sas]

Hi: Typically, when you use TAGATTR as a style override, you are using it with ODS TAGSETS.EXCELXP or ODS EXCEL. The values for tagattr attributes are typically Microsoft-specific syntax for performing various cosmetic changes.

Cynthia

[KonstantinVasil]

I figured that out about the microsoft specific syntax. So for example, is there an easy alternative for rotating text to 90 degrees, for example with escape modifier?

[Cynthia_sas]

Hi:
Not with PDF. As far as I know, the tagattr for rotate only applies to Excel. I am not sure it even works for RTF.
Cynthia

[DWilson]

Well, SAS "now" isn't the tool for creating complicated excel spreadsheets. It used to be when DDE was readily available. Without DDE its capabilities are seriously diminished.

In particular, the push to use SAS on a grid with Linux means that VBA really isn't an option in terms of integrating SAS with calls to VBA which makes creating an integrated process to create the data with SAS and produce the tables with VBA annoying.

[DWilson]

I think your life will be easier if you trying using R with SAS. At least that way SAS program(s) are submitted which produce your results.

You'll have to call R from within SAS (you can do that with SAS IML), pass your data to be written to R via SAS IML, and submit R code to R via SAS IML to write your data to the excel file. Use the openxlsx or XLConnect R packages.

I haven't seen any other solution that preserves the notion of SAS pushing data to excel.

See this paper for example code to get you started:
https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-109_Final_PDF.pdf

[Reeza]

The XLSX package with R is good as well.