topic Create excel spreadsheet with different results on sheets in spreadsheet in ODS and Base Reporting

Create excel spreadsheet with different results on sheets in spreadsheet

HyunJee — Tue, 17 Jul 2012 15:32:37 GMT

I have a dataset which contains diagnosis information. Specific diagnoses were assigned into different groups. I wanted to create an excel document that listed all the diagnoses within each of the different groups, but I wanted each group to have its own unique sheet. I know that in proc report you can specify that there be a page break based on a variable - I essentially want to do the same thing in excel except for instead of being a page break there is a new sheet.

I appreciate any help anyone can give. Thanks.

Re: Create excel spreadsheet with different results on sheets in spreadsheet

Cynthia_sas — Tue, 17 Jul 2012 15:56:01 GMT

Hi:

If you use ODS TAGSETS.EXCELXP, you can automatically create multi-sheet workbooks. In particular, the default with TAGSETS.EXCELXP destination is to create a new sheet for every table. Since PROC REPORT with the PAGE option creates a new table for every page variable, that means you would get a new sheet for every PAGE break.

There have been many previous forum postings on the use of ODS TAGSETS.EXCELXP to create multi-sheet workbooks. Many of the postings have code examples. I'd suggest you start by searching the forum for some previously posted examples.

cynthia

Re: Create excel spreadsheet with different results on sheets in spreadsheet

HyunJee — Tue, 17 Jul 2012 17:21:36 GMT

Thank you Cynthia. I had not previously used the ODS tagsets before. Thanks again.