Hi @Babloo
Not sure if you looked into the GitHup link that was embedded in the last article link I sent you "Create a SAS Viya Job with a prompt using HTML Prompts"
, but this is what you could have found there
https://github.com/xavierBizoux/SAS/blob/master/jobPrompt/index.html
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Job with Dynamic prompts</title>
</head>
<script src="/SASJobExecution/resources/sap/ui/thirdparty/jquery.js"></script>
<script src="/SASJobExecution/resources/dynamic.min.js"></script>
1. These two <script src...></script> tags will load custom built JavaScripts that contain all the needed components to populate Html widgets from a SAS data set.
2. Here is a sample how SAS data set's values loaded into the HTML Widget, and honors Selection dependency across widgets (Cascading Prompts)
<details>
<summary>Geography</summary>
<div>
<label for="var_origin">Choose a geographic location:</label>
<select name="var_origin" id="var_origin" data-colname="origin" data-library="SASHELP" data-table="CARS"></select>
</div>
</details>
<details>
<summary>Car's characteristics</summary>
<div>
<label for="var_type">Choose one or multiple car types:</label>
<select name="var_type" id="var_type" data-colname="type" multiple size="10" data-library="SASHELP" data-table="CARS" data-uses="var_origin"></select>
</div>
<div>
<label for="var_driveTrain">Which kind of drive train do you want:</label>
<select name="var_driveTrain" id="var_driveTrain" data-colname="drivetrain" data-library="SASHELP" data-table="CARS" data-uses="var_origin, var_type"></select>
</div>
</details>
Let me explain
- Geographic Location: SASHELP.CARS.Origin (id=var_origin)
- Car Type: SASHELP.CARS.Type (id=var_type) (data-uses="var_origin" ----> Data Filter) Only display types related to selected origin
- Drive train: SASHELP.CARS.Drivetrain (id=driveTrain) (data-uses="var_origin, var_type" ----> Data Filter) Only display drivetrain related to selected Origin and type(s)
With that said, based on your data size, I would highly recommend considering splitting your data based on your desired Selection Navigation Path, in OLAP term, "Drill Hierarchy".
i.e. Rather than keeping one single data set with multiple columns, that you have to keep filtering as you select more and more values for the various columns in the data. Split the data set into multiples of 2-3 columns as you fit, based on how you want your users to navigate the selections.
Hint: You can use Proc Summary/Means with the TYPES statement to generate the various aggregations/columns-combination in a single Proc step, then you can use a data step to split it into multiple smaller data sets with Indexes applied to filtering variables where needed.
You could ask, "Why should I split the data"?
Well, you don't have to, but here is why you should it consider it:
1. BASE SAS data set can only use single index in its where clause. That index can be simple (single column) / Complex (multiple columns). So as the Filtering/Selection conditions starts to build-up, the underlying query will start to take longer time to get back with the results to populate the HTML widget. This is bad in a Web App!!
2. The only other BASE data set format that can use multiple Indexes in a where clause would be SPDE. You could experiment with copying your data into an SPDE format, and create index on every filtering column. But then you'll have to deal with changing your Back-End ETL/DataPrep routines that manages updates to the data.
I recently applied this splitting technique on a Viya Job prompts against a data set that had 6+ millions records, and was 12 GB in size. The Prompts where unusable and users kept complaining on how slow the app was. After re-working the prompts to use multiple split data sets, the prompts started to get populated much faster, and it started to feel more fluid 😊
