Getting the Job Done: Connecting SAS Viya Jobs with SAS for Microsoft 365

[#1]

SAS Viya jobs are a great tool to simplify commonly run SAS programs and provide users the ability to customize tables and reports. This blog will demonstrate how to use SAS Viya jobs with SAS for Microsoft 365. While the demonstrations in this blog were conducted using the Excel desktop app, they can also be performed in the web app.

What is a SAS Viya Job?

SAS Viya jobs can be used for a wide variety of purposes, such as web reporting, performing analytics, building web applications, and delivering content to clients. A SAS Viya job includes both a program and its definition, which contains details like the job name, author, and creation date and time. Users interact with the job by making selections using HTML forms or task prompts, and those selections are passed to the SAS program as macro variables. The SAS program runs, and the results are provided back to the user.

For more information on SAS Viya jobs, visit SAS Help Center: SAS Studio Developer’s Guide: Working with Jobs or check out the Working with SAS Viya Jobs course.

What is SAS for Microsoft 365?

SAS for Microsoft 365 enables users to seamlessly integrate the capabilities of SAS with Microsoft applications. Users can add reports directly to Excel workbooks, PowerPoint presentations, Outlook emails, and Word documents. They can also add reports and data created with SAS programs and SAS Viya jobs to Excel workbooks.

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

Use Case: Dynamic Prompts Using CARS Data

Let’s take a look at a sample Viya job titled Dynamic Prompts using CARS data. This job utilizes the SASHELP.CARS table and allows users to filter vehicles by make and model, then creates a report on the selected vehicles.

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

Note: If you would like to follow along while reading this blog, you can install the Dynamic Prompts Using CARS Data SAS Viya job by following these steps:

1. Log into your SAS Viya environment. From the applications menu in the upper left corner, select Develop Code and Flows to open SAS Studio. Notice the URL ending /SASStudio/. Change it to /SASJobExecution/. 
2. Next, click the Samples icon in the left navigation menu 

   to access the samples administration page, where you can view a table of available sample jobs.

3. To install the Dynamic Prompts Using CARS Data SAS Viya job, select the job and click Copy To. In the folder selector window, select a destination folder where you have writable access and then click OK to copy the sample job. If an item already exists in that location, an option to skip, replace the item, or quit copying is presented. 

To open SAS for Microsoft 365, click SAS Viya --> Home from the top ribbon of Excel.

To access the job, go to the Reports tab and navigate to the folder where the Dynamic Prompts using CARS data job is stored.

Right-click the job and open it in the Results tab.

The Results tab allows users to select values of prompts, view the log, and view the results of the job. In this example we’ll choose Saab for the vehicle make, Sedan as the vehicle type, and will not show the SAS log.

Once I’ve chosen values for each prompt, I click Run   and view the results of the job. We see a report with information on Saab sedans including the Model, Engine Size, Horsepower, and MSRP.

To add this output to my Excel workbook, I first select where in the worksheet I want to insert the report. In this case, I choose the top left corner of my worksheet.

In the SAS Viya pane, I click the Insert in document icon.

Now the report appears in my worksheet in the location I selected.

Let’s say I instead wanted the report to display information on BMW SUVs. I change my prompts and run the job.

The updated report appears in the Results tab.

To update the report in the Excel workbook, I click the Update in document icon and the report is updated in the Excel worksheet. I also rename the Excel worksheet to BMW Report by double-clicking Sheet1 at the bottom of the worksheet and renaming it.

Now, say I wanted both the Saab and BMW reports, just on different sheets. I would first unlink the BMW report from SAS by selecting the report in the worksheet, clicking the Selected Object button on the top ribbon, and selecting Unlink from SAS. Doing this disconnects the report from the Viya job and it can no longer be updated using SAS data.

Now that the BMW report is disconnected from the Viya Job, I create a new Excel worksheet by clicking the plus at the bottom of the worksheet and rename Sheet2 to Saab Report.

I click the top left cell of the worksheet as the location of the report, then go to the SAS Viya pane to select Saab as the vehicle make and Sedan the vehicle type and run the job.

Finally, I insert the report into the worksheet by clicking the Insert in document icon.

Conclusion

SAS for Microsoft 365 is a great way to combine the powers of SAS Viya jobs and Excel. For more information on using SAS Viya jobs in Excel, SAS Help Center: Working with SAS Viya Jobs in Excel.

Find more articles from SAS Global Enablement and Learning here.