Diving Deeper: How to Create Drill-Down Functionality with SAS Viya Jobs

1 Like

A SAS Viya job is an excellent way to automate SAS programming and allow users who are not programming experts to interact with SAS programs, generate results, and create reports using custom point-and-click prompts and forms. However, there might be times when one job alone doesn’t accomplish everything you need. In that case, you may need to use multiple jobs in conjunction with one another. By linking two jobs together, you can allow a user to “drill down” from a general result to a more specific result. In this article, I discuss how to link jobs to create drill-down functionality.

Overview

Imagine that I have data about sales across several different product lines. I want to create a job that displays general sales numbers for each product line, as shown below:

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

However, I also want to allow users of this job to drill further down and view information about the profitability of each of the individual product categories within each product line, as shown here:

Rather than having two separate jobs that run independently, I’d like to create a more cohesive experience for the user by linking them. I’d like to turn the Product Line value in the first result into a hyperlink that executes the second job, with the appropriate parameters passed along.

I need to build a column that includes URLs that will call a particular job for the corresponding Product_Line value. For example, I want the Children product line to link to the following URL:

https://server.demo.sas.com/SASJobExecution/?_program=/Programs/DrillDown_Specific&_action=wait&product_line=Children

This will involve two steps:

Defining the base URL
Passing the necessary parameters

Defining the Base URL

The code used to generate the first, general report is a simple PROC PRINT step, using a table called work.sales_summary.

proc print data=work.sales_summary noobs label;
  var product_link profit;
  sum profit;
  label product_link = 'Product Line'
        profit       = 'Profit';
  format profit dollar11.;
run;

The hyperlink in the product_link variable displayed in the results needs to point to the job submit URL of the second job, with a parameter containing the value of Product_Line that will be used in the second job to filter the results. In order to create the hyperlink values in the results of the first job, we need to first create a base URL that contains the job submit URL, including the Product_Line parameter, but without a value. I can do that with the following code:

%let BASE_URL=&_URL.?_program=/Programs/DrillDown_Specific%nrstr(&_action)=wait%nrstr(&product_line=);

&_URL is a reserved macro variable that contains the URL of the web server. Next, the program= value contains the path to where the second job is stored. In this case, it’s stored in a SAS Content folder called Programs, and the job itself is called DrillDown_Specific.

Finally, I need to pass some additional parameters to the second job in the URL: the _action parameter and the product_line parameter. The value of wait for the _action parameter means that a loading screen will display while the job executes. The product_line parameter supplies a value for a macro variable used in a WHERE statement in the second job to filter the results to just the selected product line. Both of these need to be enclosed in the %nrstr() macro function so that macro triggers are not resolved, and the string is treated as plain text.

Notice I’m not specifying any value for the product_line parameter.

Passing the Necessary Parameters

Once the base URL macro variable has been constructed, I need to create the product_link variable and pass the correct parameters to the second job. In this step, I will build the work.sales_summary table used in the final general report.

To do this, I’ll use ODS inline formatting, which means that I’ll need to define an ODS escape character:

ods escapechar='^';

Next, within a DATA step, I’ll create the product_link variable and assign it an appropriate length.

length product_link varchar(1024);

Finally, I need to build the complete job submit URL for the second job, containing the value of product_line. I need to display this as a hyperlink, with the product_line value as the text.

The general ODS inline formatting syntax for creating hyperlinks is:

^{style [url='URL-of-second-job']link-text}

In this case, I want the URL to be the BASE_URL macro variable, followed by the value of Product_Line, and I want the link text to be the value of Product_Line.

product_link = "^{style [url='&BASE_URL" || 
              urlencode(strip(Product_Line)) || 
              "']" ||
              strip(Product_Line) ||
              '}';

Here, the STRIP function removes any trailing blanks in the Product_Line, and the URLENCODE function formats the parameter values. The value of Product_Line is appended to the end of the BASE_URL.

Altogether, the DATA step looks like this:

data work.sales_summary;
    set work.sales_summary;
    length product_link varchar($1024);
    product_link = "^{style [url='&BASE_URL" || 
                  urlencode(strip(Product_Line)) || 
                   "']" ||
                  strip(Product_Line) ||
                  '}';
run;

Because I’m doing this in an assignment statement in a DATA step, the product_link variable will be generated for each row in the table, creating a unique hyperlink for each value of the product_line parameter. This hyperlink is then what I display to the user in the final PROC PRINT report.

Putting It All Together

When I put it all together, the result should look like this. When I run the first job, I’m shown a table displaying the total profit for each of the four product lines. If I click on Clothes & Shoes, the second job will execute with the value of Clothes & Shoes for the product_line parameter. That value then gets used in a WHERE statement in the second job, filtering the detailed results to just the Clothes & Shoes product line. In the end, I get the following result:

Conclusion

Linking jobs to provide drill-down functionality allows you to use the results of one job to drive another. These techniques can be combined with other common job enhancements, such as prompt forms or HTML forms, to allow further customizable and flexibility. For more information about some of the other ways you can customize SAS Viya jobs, see the following articles: