08-28-2014 01:13 PM
Hello everyone. I'm part of a group of folks at my organization who are just starting to migrate from BrioQuery to a SAS environment. I state this to indicate that while we have very little SAS specific exposure, we aren't afraid of code per se.
That being said, I'm wondering how we would go about exporting SAS summary tables in excel format. Some background. Our reporting suite is primarily excel based, and to populate our reports, we've written some macros that clear out hidden tabs of a certain color, navigate to a separate extract staging area, and copy fresh extracts (that had been refreshed overnight to reflect prior day data) where the file names = tab names. The extracts are basically summary tables (currently exports of Brio Pivot tables) where through the use of arrays, SUMIFs, SUMPRODUCTS, VLOOKUPs etc we're able to represent the data in our report shells in a fully automated fashion.
Brio Pivot tables almost perfectly resemble SAS Summary Tables, and if we were able to export unformatted summary tables in excel format (.xls or .xlsx) as a step in the project, that would clear a pretty large roadblock in our transition. Obviously, the menu driven export functionality doesn't allow for this, so I'm hoping there's some sort of workaround via a bit of code that we can experiment with. Any feedback would be greatly appreciated, and thanks in advance.
09-02-2014 11:25 AM
If you have SAS 9.4, you could try the new (experimental) ODS EXCEL destination, which would allow you to deposit your Summary Tables output (PROC TABULATE) into a spreadsheet.
See this blog post:
Example of PROC TABULATE output in Excel attached.
Generated by this code:
09-02-2014 11:45 AM
You can use PROC EXPORT to generate XLS files, no menu clicking is required.
I would recommend generating XLSX files if your downstream application can use them.