Exporting Summary Tables in MS Excel

[mikestorm]

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.

[ChrisHemedinger]

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:

Experimenting with ODS EXCEL to create spreadsheets from SAS - The SAS Dummy

Example of PROC TABULATE output in Excel attached.

Generated by this code:

ods excel file="c:\temp\cars_tab.xlsx";
PROC TABULATE
DATA=SASHELP.CARS
   
    ;
   
    VAR MPG_City MPG_Highway;
    CLASS Make / ORDER=UNFORMATTED MISSING;
    CLASS Origin /   ORDER=UNFORMATTED MISSING;
    TABLE /* Row Dimension */
Origin*
  Make*
    Mean={LABEL=""},
/* Column Dimension */
MPG_City
MPG_Highway         ;
    ;

RUN;
ods excel close;

Chris

[Tom]

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.