Creating a SAS® Stored Process that generates multiple spreadsheets in Microsoft Excel when opened using the SAS® Add-In for Microsoft Office


Overview

This sample provides a way to direct stored process output to more than one worksheet. The key to achieving this goal is to use the SAS procedure PROC PRINT with several grouping options.

About the sample stored process

As the input table for the stored process, this sample uses SASHELP.SHOES. If you want to follow along with the sample, copy and save the code shown below. For this example, the stored process is named shoes.sas. Make note of the physical location. You will need it later when you register the stored process in metadata.

*Processbody;

%stpbegin;

proc print data = sashelp.shoes;
  pageby region;
  by region;
run;

%stpend;

Register the stored process

In SAS Management Console, click the Folders tab. Select SAS Folders ► Shared Data ► StoredProcesses. Right-click on StoredProcesses and select New Stored Process to launch the New Stored Process wizard. Complete the wizard to register the stored process. For assistance at any step, click Help on the wizard page.

Opening the stored process in Microsoft Excel

  1. Open Microsoft Excel.
  2. On the SAS tab, select Tools ► Options.
  3. On the Results tab, select the option Place grouped analyses on new worksheets.

4. Click OK.

5. On the SAS tab, click Report in the Insert group, and select the stored process that you saved previously. You should see results similar to the following, showing that multiple worksheets were created.

Additional Documentation

For more information about using the SAS Add-In for Microsoft Office, see the SAS Add-In for Microsoft Office documentation page.

 

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.