The SAS ADD-IN for Microsoft Office adds a
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:
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:
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:
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:
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 SAS Enterprise Guide.
These tasks are broken down into the following categories:
For this demonstration, you will
4. If prompted, enter the user name and password provided by your instructor.
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:
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.
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 user name and password provided by your instructor.
b. Navigate to the stored processed created earlier by expanding the tree (select Stored Processes ÐTraining).
c. Select Orion Customer List.
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).
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, 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 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.
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 on 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.
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.
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).