SAS Viya: Using Cascading Prompts in a Custom Step
With the July 2022 stable release (2022.1.3) there is now the capability to create cascading prompts in Custom Steps. You create prompt hierarchies to create a data dependency between controls.
In this post, I will walk you through an example of creating a custom step with cascading prompts. For my example, I will create a Custom Step with cascading prompts where the State field selection filters the selections for County and that in turns filters the City data selections. I will use the SASHELP.ZIPCODE table as the input for this example. Therefore, the STATENAME selection will determine the selections available for COUNTYNM and that selection will determine the selections available for CITY.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
Design Custom Step with Cascading Prompts
On the Steps pane of SAS Studio, select 
First thing I need to do is name the page by adding a label to the properties for the page control. Then, I save the step and give it a name.
Next, I add an Input Table control and Output Table control to the canvas and save the step. I leave both checked as required since for the step I am creating there should always be an input and output table. When using a custom step in a SAS Studio Flow, these controls are hidden and show up as input and output ports on the step.
Now I add controls to build my cascading prompts. First, I add a Column Selector control for my State field. I set its properties as shown in the screenshot below. Note that I have added STATENAME as the default selected name of the column. The user can change that if desired, but this saves some time since I am building my hierarchy of cascading prompts in a specific manner.
Next, I add a Drop-down List control for the selection of the State name. I set its properties as depicted in the screenshot below.
For my second prompt, I add a Column Selector control for my County field. I set its properties as shown in the screenshot below. Note that I have added COUNTYNM as the default selected name of the column.
Next, I add a Drop-down List control for the selection of the County name. I set its properties as shown in the screenshot below.
Since I want this drop-down prompt to be filtered by the State selection, I select 
I select 
Select OK to add the prompt hierarchy. Expand Dependencies to view that it was added as the Filtering dependency for the selectCounty control.
For my third prompt, I add a Column Selector control for my City field. I set its properties as shown in the screenshot below. Note that I have added CITY as the default selected name of the column.
Next, I add a List control to allow the user to select 1 or more city names. I set its properties as shown in the screenshot below.
Since I want the City names to only display the cities within the selected County, I select 
I select 
Select OK to add the prompt hierarchy. Expand Dependencies to view that it was added as the Filtering dependency for the selectCities control.
I save the custom step and select Preview to preview the user interface design for the custom step.
Add Program Logic to Filter Rows Based on Cascading Prompt Selections
Now that I have the user interface designed, I can add my programming logic to output only the records from the input table that match the cascading prompt selections for State, County and Cities. Here is the code:
/* Create table filtered based on State, County, and City selection */
%macro filtered_list ;
/* Create list_selections variable based on selected Cities */
%let list_selections= ;
%do i = 1 %to &selectedCities_count ;
%if &i=1 %then
%let list_selections=%str(%')&&selectedCities_&i%str(%') ;
%else %let list_selections=&list_selections,%str(%')&&selectedCities_&i%str(%') ;
%end ;
%put list_selections=&list_selections ;
/* Create output table based on State, County and City selections */
proc sql ;
create table &outputtable1 as
select * from &inputtable1
where &cityCol_1_name in (%UNQUOTE(&list_selections)) AND &stateCol_1_name="&selectedState" AND &countyCol_1_name="&selectedCounty";
%mend ;
%filtered_list ;
Test the Custom Step in a SAS Studio Flow
I can now test the custom step in a SAS Studio Flow by right-clicking on the step and selecting Add to flow.
This action adds the selected to a SAS Studio Flow file.
I add the SASHELP.ZIPCODE table to the flow and connect it to the custom step at its input port.
I can now fill out the prompts for my custom step. Note that pre-selected field names are selected. Also, note that the County value is grayed out and no City values are displayed. These items cannot be selected until the State value is selected.
I select the values for my custom step as depicted below.
Once I select the State value of North Carolina only counties in North Carolina are available for selection for County value. Then once I select the Country value of Wake only Cities in that country are available for selection. I can test this on other State and County values to ensure my hierarchy logic is working
I save and run my flow. The flow runs successfully, and the output port only contains the records from the ZIPCODE table where STATENAME=North Carolina and COUNTYNM=Wake and CITY = (Apex, Cary, or Holly Springs).
Summary
For more practice on creating cascading prompts in Custom Steps, review the Custom Step documentation. There is also a Cascading Prompts custom step starter template you can review in the SAS Studio application.
Find more articles from SAS Global Enablement and Learning here.
