topic Re: Multiple horizontal reports on same Excel sheet in ODS and Base Reporting

Multiple horizontal reports on same Excel sheet

KonstantinVasil — Fri, 22 Feb 2019 14:03:16 GMT

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:

Prepare the reports in some kind of grid container with specific position of each report within the grid - then export the container.
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!

Re: Multiple horizontal reports on same Excel sheet

RW9 — Fri, 22 Feb 2019 14:30:15 GMT

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.

Re: Multiple horizontal reports on same Excel sheet

KonstantinVasil — Fri, 22 Feb 2019 15:24:31 GMT

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.

Re: Multiple horizontal reports on same Excel sheet

Reeza — Fri, 22 Feb 2019 16:09:40 GMT

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.

Re: Multiple horizontal reports on same Excel sheet

KonstantinVasil — Fri, 22 Feb 2019 16:18:45 GMT

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:

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

Re: Multiple horizontal reports on same Excel sheet

Reeza — Fri, 22 Feb 2019 16:25:51 GMT

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.

Re: Multiple horizontal reports on same Excel sheet

Reeza — Fri, 22 Feb 2019 16:31:21 GMT

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

Re: Multiple horizontal reports on same Excel sheet

RW9 — Fri, 22 Feb 2019 20:24:00 GMT

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.

Re: Multiple horizontal reports on same Excel sheet

KonstantinVasil — Wed, 27 Feb 2019 09:53:04 GMT

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

Re: Multiple horizontal reports on same Excel sheet

Cynthia_sas — Wed, 27 Feb 2019 14:47:02 GMT

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

Re: Multiple horizontal reports on same Excel sheet

KonstantinVasil — Wed, 27 Feb 2019 15:35:02 GMT

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?

Re: Multiple horizontal reports on same Excel sheet

Cynthia_sas — Wed, 27 Feb 2019 16:20:17 GMT

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

Re: Multiple horizontal reports on same Excel sheet

DWilson — Fri, 17 May 2019 13:08:20 GMT

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.

Re: Multiple horizontal reports on same Excel sheet

DWilson — Fri, 17 May 2019 14:48:40 GMT

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

Re: Multiple horizontal reports on same Excel sheet

Reeza — Fri, 17 May 2019 20:11:28 GMT

The XLSX package with R is good as well.

Re: Multiple horizontal reports on same Excel sheet

DWilson — Fri, 17 May 2019 21:26:45 GMT

We had some issues with java on Linux and extraordinary amount of memory usage with xlconnect which requires java like the xlsx package. We got it to work generally but it requires each user to set java memory limits. Openxlsx is java free and avoided that issue.