Using the SAS Add-In for Microsoft Office - SAS BI

The SAS ADD-IN for Microsoft Office adds a

• SAS menu to the menu bar
• SAS Analysis toolbar
• SAS Data Analysis toolbar (Excel only).

SAS Menu for Microsoft Excel

The SAS menu in Microsoft Excel provides access to the functionality of the SAS Add-In for Microsoft Office, including the following:

• Ability to run stored processes or SAS task
• Tools to work with a SAS data source
• Options for the SAS Add-In
• Ability to specify the server
• The style manager to customize the appearance of results returned to Microsoft.

SAS Menu for Microsoft Word

The SAS menu in Microsoft Word provides access to the functionality of the SAS Add-In for Microsoft Office, including the following:

• Ability to run stored processes or SAS task
• Options for the SAS Add-In
• Ability to specify the server
• The style manager to customize the appearance of results returned to Microsoft

SAS Add-In Options – Excel

The SAS Add-In for Microsoft Office options for Excel is specified by selecting SAS Ð Options….

SAS Add-In Options– Word

The SAS Add-In for Microsoft Office options for Word are specified by selecting SAS Ð Options….

SAS Analysis Tools Toolbar

The SAS Analysis Tools toolbar provides access to some of the same options as the SAS menu.
Some of the tasks that can be performed include the following:

• Changing the active data source
• Showing labels in the first row
• Refreshing the results
• Viewing the data
• Browsing SAS programs
• Exporting results to MS Word
• Accessing your SAS Favorites.

Related Article: SAS BI Interview Questions

SAS Data Analysis Toolbar

The SAS Data Analysis toolbar provides access to options for working with SAS data.
The tasks that can be performed include the following:

• Opening a SAS data source
• Navigating through the data
• Using a filter
• Sorting the

The SAS Data Analysis toolbar is not available in Microsoft Word.

Filtering SAS Data in Microsoft Excel

The SAS Add-In for Microsoft Office can filter SAS data before bringing it into Excel.

Selecting opens an Expression Editor window for creating more advanced expressions.

Sorting SAS Data in Microsoft Excel

The SAS Add-In for Microsoft Office can also sort SAS data before bringing it into Excel.

Restricting SAS Columns in Microsoft Excel

The SAS Add-In for Microsoft Office provides a mechanism to select which columns are displayed.

Built-In SAS Tasks

The SAS Add-In for Microsoft Office provides access to most of the same tasks found in the SAS Enterprise Guide.
These tasks are broken down into the following categories:

• ANOVA
• Basic Analysis
• Capability
• Control Charts
• Data
• Describe
• Graph
• Multivariate
• Pareto
• Regression
• Survival Analysis
• Time Series

Related Article: Learn New Features of SAS Enterprise Guide 3 in SAS BI

Using the SAS Add-In for Microsoft Office

For this demonstration, you will

• Set the SAS Add-In for Microsoft Office options
• Run stored processes
• Access a SAS data source
• Show filtering and sorting
• Run a built-in SAS task.

1. Invoke Microsoft Excel by selecting Start Ð All Programs Ð Microsoft Office Ð Microsoft Excel.
2. Select SAS Ð Options from the pull-down menu.
3. The SAS Add-In displays an information window while it connects to the SAS Metadata Server.

4. If prompted, enter the user name and password provided by your instructor.

Select 

5. The Options window opens with the Data tab selected.

This tab is where you can set the number of observations to display as each “page” of data is requested from the SAS Server.
6. Select the Results tab.

This tab can be used to set how the results are returned, HTML or CSV.
Using this tab you can also select whether or not the SAS log should be displayed.
7. Select the Graph tab.

This tab is where you can select the image format for graph. The valid values are as follows:

• ActiveX
• ActiveX image
• GIF
• JPEG
• PNG

8. Select the Tasks tab.

9. Change the default SAS server to use SASMain instead of Local (the default).

10. Select the Stored Processes tab.

11. Select to close the Options window and save the changes.

Run a Stored Process Using the SAS Add-In for Microsoft Office

12. Select SAS Ð Browse SAS Programs from the pull-down menus (or select on the toolbar).
a. Expand the Stored Processes tree (select Stored Processes Ð Samples) and select Stored Processes.
b. Select Sample: Frequency Analysis of Municipalities.

Sample stored processes are automatically provided with the Web Infrastructure Kit. These samples can be run to verify that the system is configured properly and to sample the capabilities of stored processes.
c. Select 

The program results are streamed back to an Excel worksheet which is given the name of the stored process.

The results are displayed in a read-only worksheet, so the values cannot be updated. To edit the worksheet, select Tools Ð Protection Ð Unprotect Sheet….
If you always want the sheet unprotected, you can specify that by selecting SAS ÐOptions and then deselecting the Protect data worksheet check box on the Data tab.

Send Results from Excel to Word

13. Select SAS Ð Send to Microsoft Word (or select from the toolbar).

When an analysis is selected to be sent to Word, the stored process runs again to incorporate any changes that were made to the data source before the analysis is seen. After the output is available in the Word document, the document can be edited and new text is added using the functionality of Microsoft Word.

14. You can also run stored processes directly from Microsoft Select SAS ÐBrowse SAS Programs.
a. If prompted, enter the username and password provided by your instructor.

b. Navigate to the stored process created earlier by expanding the tree (select Stored Processes ÐTraining).
c. Select Orion Customer List.

Select 
15. Because this stored process has a parameter, you are prompted to enter a Type 10 and select

The output from the Stored Process is streamed back to the cursor position in the current document unless the Insert results into current document option is deselected on the Results tab of the SAS Options in Word.
16. Close Word by selecting File Ð Exit (do not save the changes).

Managing Favorites

17. Back in Excel, select SAS Ð My SAS Favorites Ð Manage Favorites….
18. Add a stored process sample to My SAS Favourites.
        a. Expand Stored Processes and Samples, select Stored Processes.
        b. Right-click Sample: Frequency Analysis of Municipalities.
        c. Select Add to Favorites.

19. Add another stored process to My SAS Favourites.
        a. Select Training.
        b. Right-click Orion Customer List.
        c. Select Add to Favorites.

20. Add a Task to My SAS Favourites.
        a. Expand SAS Tasks and Describe.
        b. Right-click One-Way Frequencies.
        c. Select Add to Favorites.

21. Close the Manage Favorites window by selecting 
22. Select My SAS Favorites from the toolbar, and notice that the newly added favorites are on the list:

23. Organize favorites by selecting Manage Favorites….
24. Select the New Favorites Folder icon () and type Samples as the name of the new folder

25. Drag the Sample: Frequency Analysis of Municipalities and drop it into the new folder Samples.
26. Select to close the Manage Favorites window.

Add SAS Data to Excel

The SAS Add-In for Microsoft Office allows Excel to access SAS data from a server or your local machine and add it to an Excel workbook.
27. Select SAS Ð Open SAS Data Source from the pull-down menus (or select from the toolbar).
a. Select Servers from the Shortcut Bar, then choose SASMain.

Select 
b. Select Orion Star Gold Library, then choose 

c. Select CUSTOMER_DIM, then choose 

If you cannot read the entire name of a table, use the View Mode icon on the toolbar to change the view to Detail or List.
The first “page” of data is streamed back to an Excel worksheet which is given the name of the library reference and table name.

Working with Data in Excel Using the SAS Add-In

Excel limits the number of rows available in a worksheet to 65,536 and the columns to 256.

By adding data sources to your workbook via SAS, you can open data sources that are larger than 65,536 rows or have more than 256 columns or both. The SAS Add-In has options to set a number of rows to view and provides a mechanism to select only the columns you want to see.

The SAS Data Analysis toolbar can be used to navigate through the data, apply a filter, sort the data, and restrict which columns to display.

28. Click on the range of records (1-5000) from the SAS Data Analysis toolbar to change the starting point (or select SAS Ð Navigate SAS Data Source Ð Go To Record).

29. Type in a value of 4000, then click on  

The worksheet now displays records 4000-8999.
The default number of rows that is displayed can be changed from the Data tab of the SAS Options window in Excel.

The arrow tools on the SAS Data Analysis toolbar allows scrolling through the data. The arrows with the bars next to them take you all the way to the beginning or end of the data. The single arrows scrolls one “page” of data at a time (5,000 rows by default).
30. Select (or SAS Ð Navigate SAS Data Source Ð Go to End).
31. Select (or SAS Ð Navigate SAS Data Source Ð Go to  Start).

SAS Data Can Be Subset in Excel by Using a Filter

32. Filter the data so that only United States customers who are also Orion Star Group Members are displayed.
a. Select the SAS Data Analysis toolbar (or select SAS Ð Filter SAS Data Source).
b. Select in the first box and select the desired column name, Customer Country.

c. Select  in the second box and specify the filter criteria, Is equal to.

d. Select  in the third box and select the desired value, US.

e. Select 
f. Select  in the last box and specify a condition (AND) in order to create combination filter.

g. Complete the new line of the filter for Customer  Group  Name Is equal to Orion  Club  Gold  members.

h. Select  to validate the filter.

i. Select  to close validation window.
j. Select  to open the Advanced Expression Editor window.

The Advanced Expression Editor is very similar to the editor in SAS ETL Studio.
k. Select  to close the Advanced Expression Editor window and apply the specified filter to the data.
l. Select OK to close the More Data Options window and apply the specified filter to the data.
The expression is evaluated on the Workspace Server and the first “page” of filtered data is returned to Excel.

 to  to indicate that a filter is applied.

SAS Data Can Be Sorted in Excel by Specifying a Sort Criteria

33. Sort the data by the customer’s first and last names.
a. Select  on the SAS Data Analysis toolbar, or select SAS Ð Sort SAS Data Source.
b. Select  in the box and select Customer Last Name.

c. To sort by first name within last name, select in the second box and select Customer First  Name.

d. Select  to close the More Data Options window and sort the data.
The data is sorted on the Workspace Server and the first “page” of sorted data is returned to Excel.

Built-In SAS Tasks are Helpful in Analyzing Data

34. Select SAS Ð Browse SAS Programs.
a. Expand SAS Tasks and select Describe Ð One-Way Frequencies.

b. Select .The One-Way Frequencies Experimental Wizard opens with a list of the available columns.

c. Select Customer_Age_Group and Customer_Gender as Analysis variables.

d. Select Statistics in the list on the left, then select Frequencies and percentages.

e. Select Plots in the list on the left, then select Horizontal.

Select 
The frequency task runs and displays the results in the worksheet that is given the name of the SAS task.

35. Close Excel by selecting File Ð Exit (do not save the changes).