dynamically generate multiple work sheets in one excel

sunilreddy — Thu, 07 Jun 2012 12:17:46 GMT

Hi,

I want to have dynamically generate multiple work sheets in one excel. I don't want to store the excel in any location.I want to open the excel directly instead
of open it from stored location.

When i am using below code, Below both reports are appending in one work sheet instead of two work sheets.

data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel');
rc = stpsrv_header('Content-disposition','attachment; filename=Audit Report for User Roles.csv');
run;

ods tagsets.csvall options(sheet_name='report1');

Proc report data= test1;
Run;

ods tagsets.csvall options(sheet_name='report2');

Proc report data= test2;
Run;
ods tagsets.csvall close;

Could you please help me in this.

Re: dynamically generate multiple work sheets in one excel

Cynthia_sas — Thu, 07 Jun 2012 13:41:23 GMT

Hi:

This looks like a stored process. The way a stored process works is to populate 1 sheet of 1 workbook. If you ran the stored process from within the SAS Add-in for Microsoft Office, you would get the results of both PROC REPORT steps in 1 worksheet in 1 workbook.

When you do use the SAS Add-in for Microsoft Office, you do have an interactive window that prompts you whether you want the results to go into an existing sheet or a new sheet or a new workbook. What caused you to try sheet_name as a suboption for CSVALL destination? It is not a valid option for TAGSETS.CSVALL destination. You can prove this to yourself (and see the valid suboptions) by using the

options(doc='Help')

suboption and then looking in the log for the valid suboptions for the CSVALL destination. For your convenience, the valid suboptions for CSVALL, using the SAS 9.3 install are:

==============================================================================
The CSV Tagset Help Text.

This Tagset/Destination creates output in comma separated value format.

Numbers, Currency and percentages are correctly detected and show as numeric values.
Dollar signs, commas and percentages are stripped from numeric values by default.

==============================================================================

These are the options supported by this tagset.

Sample usage:

ods csv options(doc='Quick');

ods csv options(currency_as_number='yes' percentage_as_number='yes' delimiter=';');

Doc: No default value.
Help: Displays introductory text and options.
Quick: Displays available options.

Delimiter:   Default Value ','
     Sets the delimiter for the values. Comma is the default. Semi-colon is
     a popular setting for european sites.

currency_as_number:   Default Value 'No'
     If 'Yes' currency values will not be quoted.
     The currency values are stripped of punctuation and currency symbols
     so they can be used as a number.

percentage_as_number:   Default Value 'No'
     If 'Yes' percentage values will not be quoted.
     The percentages are stripped of punctuation and the percent sign
     so they can be used as a number.

Currency_symbol:   Default Value '$'
     Used for detection of currency formats and for
     removing those symbols so excel will like them.
     Will be deprecated in a future release when it is
     no longer needed.

Decimal_separator:   Default Value '.'
     The character used for the decimal point.
     Will be deprecated in a future release when it is no longer needed.

Prepend_Equals:   Default Value 'no'
     Put an equal sign in front of quoted number values.
     This only works in conjunction with quote_by_type.

quote_by_type: Default Value 'no'
Put values based on the type, not based on what the regex match for number.

Table_Headers: Default Value 'yes'
If no, skip the header section of all tables.

Thousands_separator:   Default Value ','
     The character used for indicating thousands in numeric values.
     Used for removing those symbols from numerics so excel will like them.
     Will be deprecated in a future release when it is no longer needed.

Quoted_columns:   Default Value ''
     A list of column numbers that indicate which values should be quoted
     ie. Quoted_columns="123"

Bylines: Default Value: No
If yes bylines will be printed

Titles: Default Value: No
If yes titles and footnotes will be printed

Notes: Default Value: No
If yes Note, Warning, Error, and Fatal notes will be printed

Proc_Titles: Default Value: No
If yes titles generated by the procedures will be printed

==============================================================================

As you can see, there is no "sheet_name" suboption for TAGSETS.CSVALL. Therefore, when you used this suboption, it was ignored. Even if you create a CSV file using the CSVALL destination in SAS, without using the BI Platform or stored processes, you would get the results of both PROC REPORT steps in one worksheet -- this is how Excel would open the CSV file. So, the bottom line is that TAGSETS.CSVALL will never give you a multi-sheet workbook.

You might have read something about TAGSETS.EXCELXP -- this is a destination which DOES create multi-sheet workbooks and DOES have a sheet_name suboption. There are many postings in the Stored Process Forum about using TAGSETS.EXCELXP with a stored process on the BI Platform to create a multi-sheet workbook. When the stored process is requested using the Information Delivery Portal or the Stored Process Web Application, then you can get multi-sheet workbooks.

I'd suggest that you investigate the use of TAGSETS.EXCELXP. This destination creates Microsoft-compliant Spreadsheet Markup Language XML, as defined for Office 2003. Microsoft designed this set of XML markup tags to describe a workbook. You will need Office 2003 or higher to correctly render the XML. Another suggestion would be to post your stored process questions to the Stored Process Forum, or at the very least to search the forums for more information about creating multi-sheet workbooks using a stored process. If you continue to have problems with your stored process, then you might also wish to open a track with Tech Support. They can help you figure out how to best write a stored process given your requirements and your BI configuration.

cynthia

topic Re: dynamically generate multiple work sheets in one excel in ODS and Base Reporting

dynamically generate multiple work sheets in one excel

Re: dynamically generate multiple work sheets in one excel