Using the Add-in for Excel, Can a Stored Process Output to multiple tabs?

[Linda_V]

Hi..

We have a lot of our reports written as Stored Processes so that the report users can run their own reports as needed using the SAS Add-in for M/S Office.

We would like to create a stored process for a report that outputs to multiple tabs in excel.   Can this be done?

I have created an example using the SASHELP.CLASS directory to try and show you what we are trying to do.

When I created a stored process using this code, all of the PROC PRINTS are writing to one excel sheet.    Can they somehow be sent to separate tabs?  Thank you for your suggestions.

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='FIRST') STYLE=NORMAL;

PRINT DATA=SASHELP.CLASS (WHERE=(SEX='F'));

RUN;

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='SECOND');

PRINT DATA=SASHELP.CLASS (WHERE=(SEX='M'));

RUN;

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='THIRD');

PRINT DATA=SASHELP.CLASS;

RUN;

ODS TAGSETS.EXCELXP CLOSE;

[Reeza]

I don't know much about stored procs but what happens when you use the sheet_interval option?

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='FIRST' SHEET_INTERVAL='proc') STYLE=NORMAL;

PRINT DATA=SASHELP.CLASS (WHERE=(SEX='F'));

RUN;

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='SECOND');

PRINT DATA=SASHELP.CLASS (WHERE=(SEX='M'));

RUN;

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME='THIRD');

PRINT DATA=SASHELP.CLASS;

RUN;

ODS TAGSETS.EXCELXP CLOSE;

[jakarman]

With AMO (Addin Microsoft Office) you should not think in coding tagsets or any other usual SAS-coding.
Using AMO you will get an SAS Addin-menu eg in EXCEL with a lot of options.

You could get data into your office environment or running analyses or running Stored processes

In spread-sheet you will define in cells the links to SAS-code (stored-processes) to be executed.
The answer to your question is: -> The question is ignoring the way AMO works.

[Linda_V]

Thanks.  Do you have a website that might have some examples that I can look at ?

[jakarman]

I give you some links:
Hooking Up SAS and Excel - Part 9 - AMO - by Colin Harris - Knoware - YouTube
SAS Add-In for Microsoft Office (SAS main entry)  SAS(R) Add-In 6.1 for Microsoft Office: Getting Started in Microsoft Excel, Microsoft Word, and Micr...adding results to a workbook)

http://support.sas.com/resources/papers/proceedings12/036-2012.pdf

All ways SAS/Office integration, http://support.sas.com/resources/papers/proceedings14/1764-2014.pdf

[VinitXen]

Hi.

Sorry to apply to an old post but I am facing exactly the same issue now...Can you please share how did you solve it? I need to urgently resolve this issue.

For more info into what I am doing...I am running a stored process from Excel SAS Add-IN. Stored process generate two datasets as an output and I want both dataset into separate worksheet in Excel but currently output is in one worksheet one below the other.

Also, I am not any getting report formatting into excel such as Title Color or Backgroud...alignment etc.

Please request you to kindly share how did you resolve below mentioned issue.

[Vince_SAS]

Info from someone else in SAS R&D:

Q: For more info into what I am doing...I am running a stored process from Excel SAS Add-IN. Stored process generate two datasets as an output and I want both dataset into separate worksheet in Excel but currently output is in one worksheet one below the other.

A: If your stored process is creating output datasets, then the stored process should be able to open these if you have the option selected to open output data automatically.  This option is found from SAS > Tools > Options, on the Data tab.  Each output data set should be created on a separate tab in the workbook.  I believe there is a difference between an output dataset and the dataset created from a data step though, so only the datasets that are actually ‘output’ from a proc would be displayed this way.

Q: Also, I am not any getting report formatting into excel such as Title Color or Backgroud...alignment etc

A: This sounds like the option to "Apply the SAS style to the results" is not enabled. This option is found from SAS > Tools > Options, on the Results tab.  When rendering content the add-in can choose to apply a SAS style or leave the style blank for you to apply an Excel style.  If you don't apply the SAS style, any styling changes you make will be preserved during a refresh.  If you do apply the SAS style, then all of the styles are overwritten on a refresh, as the SAS style is applied.

Q: Can they somehow be sent to separate tabs?

A: The add-in does support results created by the ods ExcelXP tagset.  The stored process will need to force this tagset in the code though so that AMO doesn’t overwrite it with the _odsdest macro variable that gets assigned in %stpbegin.  It’s kind of a workaround approach to get this tagset to be honored.  When the code runs, a workbook object is returned to the add-in.  That workbook is simply opened, it is not tracked for refresh like other content that is inserted in-place into the worksheet.  If they use the ExcelXP tagset and doctor up their stored process to use it, then all the features of that tagset are available in the workbook that is returned.  The downside is that these results don’t take full advantage of the other features of AMO.

Vince DelGobbo

SAS R&D

[Linda_V]

Hi, 

Do you have any examples on how to send the output to two different excel tabs?  I am using 2 Proc Prints and I want them on separate tabs within the excel workbook.

Thanks, 

Linda

[Vince_SAS]

What version of SAS are you using?  You can add this code to your stored process and find the version displayed in the SAS log:

%put SAS Version: &SYSVLONG;

What version of the SAS Add-in for Microsoft Office are you using?  You can find this information by selecting SAS > Help > About SAS Add-in for Microsoft Office from Excel.

Are you using the ODS tagsets.ExcelXP destination or the newer Excel destination?  You might have better results using the newer Excel destination.

Vince DelGobbo

SAS R&D

[Linda_V]

Hi, 

Thank you.  Here is our Current Verson:  SAS Version: 9.04.01M5P091317

SAS Excel Add-in Version:  7.15 HF7 (7.100.5.6182) (32-bit)

The current Stored Process where I would like to output to multiple Tabs in the Excel report for the Add-in Users is using PROC REPORT.  I do not have any ODS coding but I am using some Style statements.

There are 3 Reports, one for each Model Tier.  Right now, I have the PROC REPORT in a macro and then execute the macro 3 times, one for each Model Tier.  So, the Add-in user gets all three reports in one Excel worksheet.  They would like the 3 reports split out into separate worksheet tabs.

Thank you.

Linda Vance

[Vince_SAS]

Following are instructions using SAS Enterprise Guide 7.15 HF8, SAS Add-in for Microsoft Office 8.2 Update 4, and 9.4 M6.

Access SAS Enterprise Guide and then create a new program with this code:

%macro create_workbook(model_tier=); 
 
*  Specify the worksheet name; 
 
ods &_ODSDEST options(sheet_name="Model Tier: &MODEL_TIER"); 
 
proc report data=sashelp.cars; 
  where (type eq "&MODEL_TIER"); 
  columns Type Make Model; 
  define Type / 'Model Tier'; 
run; quit; 
 
%mend create_workbook; 
 
%let RC = %sysfunc(stpsrv_header(Content-type, application/vnd.ms-excel));

*  Specify ODS destination; 

%let _ODSDEST=tagsets.ExcelXP;

%STPBEGIN()

*  First worksheet; 
%CREATE_WORKBOOK(model_tier=Sedan)
 
*  Second worksheet; 
%CREATE_WORKBOOK(model_tier=Wagon)
 
*  Third worksheet; 
%CREATE_WORKBOOK(model_tier=SUV)
 
%STPEND()

Right-click on the program node and the select Create Stored Process.

Specify a name and location for the stored process and then select Next.

Deselect the Include code for Stored process macros checkbox in the SAS Code dialog, and then select Next.

In the Execution Options dialog, specify a location to store the SAS code, and then select only Stream for Result capabilities.  Select Finish.

Access Excel and then run the stored process using the SAS Add-In for Microsoft Office.  A dialog with this text is presented when the stored processes completes execution:

The stored process created an Excel Workbook

The stored process forced the generation of an Excel workbook.  The SAS Add-In for Microsoft Office can open the Excel Workbook, but will not track it as refreshable content.

Select Open document to open the Excel workbook.  There are 3 worksheets named "Model Tier Sedan", "Model Tier Wagon", and "Model Tier SUV" 

Vince DelGobbo

SAS R&D