Your SAS programs, embedded in web apps and elsewhere

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

Reply
Contributor
Posts: 30

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

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;

Super User
Posts: 17,828

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

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;

Valued Guide
Posts: 3,208

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

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.

---->-- ja karman --<-----
Contributor
Posts: 30

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

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

Valued Guide
Posts: 3,208

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

New Contributor
Posts: 4

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

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.

 

 

SAS Employee
Posts: 285

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

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

Ask a Question
Discussion stats
  • 6 replies
  • 1811 views
  • 2 likes
  • 5 in conversation