Mindmajix

Using the SAS Add-In for Microsoft Office

The SAS Add-in for Microsoft Office adds a

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

Screenshot_1547

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

Screenshot_1548

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

Screenshot_1549

SAS Add-In Options – Excel

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

Screenshot_1550

SAS Add-In Options– Word

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

Screenshot_1551

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.

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.

Screenshot_1552

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

Screenshot_1554

Sorting SAS Data in Microsoft Excel

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

Screenshot_1555

Restricting SAS Columns in Microsoft Excel

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

Screenshot_1556

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:

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

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.

Screenshot_1557

  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.

Screenshot_1558

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

Screenshot_1559

SelectScreenshot_1207

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

Screenshot_1560

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.

Screenshot_1561

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.

Screenshot_1562

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.

Screenshot_1563

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

Screenshot_1564

      10. Select the Stored Processes tab.

Screenshot_1565

      11. SelectScreenshot_1207to 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 Screenshot_1617on the toolbar).

         a. Expand the Stored Processes tree (select Stored Processes Ð Samples) and select Stored Processes.

        b. Select Sample: Frequency Analysis of Municipalities.

Screenshot_1566

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. SelectScreenshot_1487

Screenshot_1567

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

Screenshot_1568

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 Screenshot_1569 from the toolbar).

Screenshot_1570

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.

Screenshot_1571

         b. Navigate to the stored processed created earlier by expanding the tree (select Stored Processes ÐTraining).

          c. Select Orion Customer List.

Screenshot_1572

SelectScreenshot_1487

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

Screenshot_1573

Screenshot_1574

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.

Screenshot_1575

      19. Add another stored process to My SAS Favourites.

         a. Select Training.

        b. Right-click Orion Customer List.

        c. Select Add to Favorites.

Screenshot_1576

      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.

Screenshot_1577

      21. Close the Manage Favorites window by selectingScreenshot_1470

     22. Select My SAS Favorites from the toolbar, notice that the newly added favorites are on the list:

Screenshot_1578

      23. Organize favorites by selecting Manage Favorites….

      24. Select the New Favorites Folder icon (Screenshot_1579) and type Samples as the name of the new folder

Screenshot_1580

   25. Drag the Sample: Frequency Analysis of Municipalities and drop it into the new folder Samples.

     26. Select Screenshot_1470to 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 Screenshot_1581 from the toolbar).

          a. Select Servers from the Shortcut Bar, then choose SASMain.

Screenshot_1582

Select Screenshot_1493

         b. Select Orion Star Gold Library, then choose Screenshot_1493

Screenshot_1583

        c. Select CUSTOMER_DIM, then choose Screenshot_1493

Screenshot_1584

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.

Screenshot_1586

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 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.

Screenshot_1587

      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).

 
   

Screenshot_1588

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

Screenshot_1589

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.

Screenshot_1590

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 Screenshot_1591(or SAS Ð Navigate SAS Data Source Ð Go to End).

      31. Select Screenshot_1592(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 Screenshot_1593on the SAS Data Analysis toolbar (or select SAS Ð Filter SAS Data Source).

        b. Select Screenshot_1594 in the first box and select the desired column name, Customer Country.

Screenshot_1595

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

Screenshot_1596

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

Screenshot_1598

         e. SelectScreenshot_1207

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

Screenshot_1599

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

Screenshot_1600

         h. Select Screenshot_1601 to validate the filter.

Screenshot_1602

        i. Select Screenshot_1207to close validation window.

         j. Select Screenshot_1603to open the Advanced Expression Editor window.

Screenshot_1604

The Advanced Expression Editor is very similar to the editor in SAS ETL Studio.

        k. Select Screenshot_1207to close the Advanced Expression Editor window and apply the specified filter to the data.

          l. Select Screenshot_1207to 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.

Screenshot_1605

The filter icon changes from Screenshot_1593to Screenshot_1606to 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 Screenshot_1607on the SAS Data Analysis toolbar, or select SAS Ð Sort SAS Data Source.

        b. Select Screenshot_1594in the box and select Customer Last Name.

Screenshot_1608

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

Screenshot_1609

        d. Select Screenshot_1207to 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.

Screenshot_1610

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.

Screenshot_1611

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

Screenshot_1612

        c. Select Customer_Age_Group and Customer_Gender as Analysis variables.

Screenshot_1613

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

Screenshot_1614

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

Screenshot_1615

Select Screenshot_1502

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

Screenshot_1616

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

 


 

0 Responses on Using the SAS Add-In for Microsoft Office"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.