SAS Programs with Excel using SAS for Microsoft 365

SAS for Microsoft 365 enables users to seamlessly integrate the capabilities of SAS with Microsoft applications. This post will focus on leveraging SAS programs to enhance Microsoft Excel workbooks using SAS for Microsoft 365. While the demonstrations in this post were conducted using the Excel desktop app, they can also be performed in the web app.

For more information on SAS for Microsoft 365, visit SAS for Microsoft 365 | SAS.

Embedding a SAS Program

Since we are in the midst of baseball season, let’s analyze the baseball table from the SASHELP library. This table includes information on players' statistics, teams, leagues, divisions, positions, and salaries.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

I want to create an Excel workbook analyzing the salaries of National League players. Upon opening Excel, I notice the SAS Viya tab in the ribbon at the top and click SAS Viya --> Home.

The SAS Viya pane appears on the right side of the workbook. Navigating to the Programs tab, I see a green checkmark next to Program1, indicating that I am connected to the SAS Computer server, which is necessary for writing or using an existing SAS program.

Renaming a Program

The Programs tab features the same tabs for code and the log that are present in SAS Studio. By default, my program is named Program1, and any additional programs would be named Program2, Program3, and so on. To rename my program, I click the More options icon → Rename program and call this program National_Analysis.

Inserting Results into a Workbook

I want to write a program that filters the baseball data to include only players in the National League, displaying their name, team, league, and salary information. As I start typing, I notice the editor color-codes keywords and provides suggestions.

data National;
set sashelp.baseball;
where League = "National";
keep Name Team League Salary;
run;

Once I finish my program, I click “Run”, and the Output Data tab appears with my new table work.National. To insert this table into my workbook, I click the Open icon, which opens the table in the Results tab. From here I can insert the table into my workbook by clicking the Insert table in document icon .

Updating Results

I go back to the code and filter to only include players whose salary exceeds 500. I run the program, and notice my workbook is not immediately updated. To fix this, I go to the Results tab and click the Update in document icon . This will apply any changes I made to the program to the results in the workbook.

data National;
set sashelp.baseball;
where League = "National" and
Salary gt 500;
keep Name Team League Salary;
run;

In my program I can create multiple tables and reports, adding them wherever I would like in the Excel workbook. For example, here I create a PROC MEANS report on Salary and insert it into the workbook.

proc means data = National;
var Salary;
run;

Saving a SAS Program

Embedded SAS programs are created within an Excel workbook with SAS for Microsoft 365. They are accessible within the workbook, however if I wanted to use this program elsewhere, let's say in a new workbook, I would not have access to the program. I can instead save any programs I create as external programs by clicking the More options icon --> Save as and navigating to the folder where I want to save the program. The program can now be accessed outside of the workbook where it was created.

Using a Previously Created SAS Program

If I want to use an external program created previously, I can simply open the program from the SAS Viya Pane by going to the Programs tab, clicking the More options icon --> Open program.

If while working with this program my teammate or boss updates it, I must refresh the program by going to the Programs tab, click the More options icon --> Refresh program. SAS for Microsoft 365 reopens the program and displays the latest version of the code.

Unlinking Results from SAS

Let’s say I want to save a copy of my results, unlinking my results from my SAS program. I can do so by selecting the results in my workbook --> the Selected Object icon in the SAS Viya ribbon --> Unlink from SAS. I notice the Update All and Selected Object options are now disabled.

Finding Results and Programs

I continue analyzing the baseball data and end up with several tables, reports, and graphics, making it hard to keep track of everything. If I want to find the objects associated with a program, I can go to the Programs tab --> select the program I am interested in, in this case American_Analysis.sas, where I analyzed the salaries of players on the American League --> the More options icon --> Find in document, and the results associated with the program will appear in the workbook.

Code to create American_Analysis.sas:

data American;
    set sashelp.baseball;
    where League = "American";
run;

proc means data=American;
    var Salary;
run;

proc univariate data = American noprint;
    histogram Salary;
run;

I can also go the opposite direction, selecting the results in the workbook --> Selected Object --> Find in SAS Pane. The program that created the results will pop up in the Programs tab.

Closing a SAS Program

To close a program in the SAS pane, I click the More options icon --> Close, or Close all programs. I can either keep or remove the inserted content from the workbook. If I keep the results in the workbook, they are disconnected from SAS and cannot be updated. To get an updated version, I would need to open and run the SAS program and re-insert the results into the workbook.

Conclusion

SAS for Microsoft 365 is an efficient way to utilize SAS and Microsoft tools together, with the Programs tab allowing users to customize their workbook to their liking. For more information on SAS for Microsoft 365, visit SAS for Microsoft 365 | SAS Support. For more information about the Programs tab, visit SAS Help Center: Working with SAS Programs in Excel.

Find more articles from SAS Global Enablement and Learning here.