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:
Any discussion and suggestions are highly appreciated!
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.
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.
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.
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:
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.
Redirecting the output to PDF brings other problems such as: rotated spanned rows using tagattr="rotate:90" not working...
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?
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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.