SAS Viya: Hardcoding the Value of an Input Table and Column in a Custom Step
As of the March stable release (2023.03) the custom step author can now specify the table and column to populate a selection list and they also have the option to hide or show that information to the end user. Let’s look at these new control options of Make control read-only and Hide control at runtime in action by reviewing a custom step I created to perform currency conversion.
In my custom step, I get the list of currencies from the CURRENCY_CD column on the SASHELP.SAS_CURRENCY table.
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 added an input table control and selected the option to Make control read-only and set the default library to SASHELP and the default table to SAS_CURRENCY to hardcode it as the reference table for the list currencies. I have labeled this input reference table as the Currency reference table. For now, I leave the Hide control at runtime option unchecked.
I then added a column selector control and linked it to my Currency reference table. I selected the option to Make control read-only and set the default column to CURRENCY_CD to hardcode it as the reference column for the list currencies. I have labeled this reference column as the Currency code column. For now, I leave the Hide control at runtime option unchecked.
I then add a drop-down list controls to select the currency to convert from and to. They both use a dynamic list and the reference column is the Currency code column.
For the rest of the custom step, I have the input table, column that contains the currency value to be converted, the new column to contain the converted currency value and the output table.
When I select Preview the Currency reference table and Currency code column are displayed, but the user is not able to change the values since those controls are set to read-only. Also, the Convert currency from and Convert currency to drop-down columns contain the list of CURRENCY_CD as their selections.
Note: When using this Custom Step in a SAS Studio Flow the Currency reference table and Currency code column controls wll be displayed inside the properties pane of the custom step since the option to hide at control at runtime.
There is also a page to enter the API key for performing the currency conversion lookup.
Here is the full Prompt UI for the currency conversion step:
{
"showPageContentOnly": true,
"pages": [
{
"id": "currConv",
"type": "page",
"label": "Currency Conversion",
"children": [
{
"id": "currencyTable",
"type": "inputtable",
"label": "Currency reference table",
"required": true,
"placeholder": "",
"visible": "",
"readonly": true
},
{
"id": "currencyCode",
"type": "columnselector",
"label": "Currency code column",
"order": false,
"columntype": "a",
"max": 1,
"min": 1,
"visible": "",
"readonly": true,
"table": "currencyTable"
},
{
"id": "convertFrom",
"type": "dropdown",
"label": "Convert currency from:",
"items": {
"ref": "currencyCode"
},
"required": true,
"placeholder": "",
"visible": ""
},
{
"id": "convertTo",
"type": "dropdown",
"label": "Convert currency to:",
"items": {
"ref": "currencyCode"
},
"required": true,
"placeholder": "",
"visible": ""
},
{
"id": "inTable",
"type": "inputtable",
"label": "Select the input table:",
"required": true,
"placeholder": "",
"visible": ""
},
{
"id": "currencyCol",
"type": "columnselector",
"label": "Select the column to convert its curency",
"order": false,
"columntype": "n",
"max": 1,
"min": 1,
"visible": "",
"table": "inTable"
},
{
"id": "convertedCol",
"type": "newcolumn",
"label": "Specify the column for the converted currency value:",
"required": true,
"placeholder": "",
"hideproperties": false,
"readonly": false
},
{
"id": "outTable",
"type": "outputtable",
"label": "Select the output table:",
"required": true,
"placeholder": "",
"visible": ""
}
]
},
{
"id": "apiKey",
"type": "page",
"label": "API Key",
"children": [
{
"id": "_apiKey",
"type": "textfield",
"label": "Specify your API key:",
"placeholder": "",
"required": true,
"visible": ""
}
]
},
{
"id": "about",
"type": "page",
"label": "About",
"children": [
{
"id": "aboutText",
"type": "text",
"text": "The \"Currency Conversion\" step uses a proc http call to a currency rate API to get today's exchange rate between two currencies. It requires an API key. Refer to the notes on the Program tab.\n\nVersion: 1.0 (29JUN2023)",
"visible": ""
}
]
}
],
"values": {
"currencyTable": {
"library": "SASHELP",
"table": "SAS_CURRENCY"
},
"currencyCode": [
{
"value": "CURRENCY_CD"
}
],
"convertFrom": null,
"convertTo": null,
"inTable": {
"library": "",
"table": ""
},
"currencyCol": [],
"convertedCol": {
"value": "converted_CurrencyValue",
"label": "converted_CurrencyValue",
"type": "n",
"format": "NLMNYIw.d"
},
"outTable": {
"library": "",
"table": ""
},
"_apiKey": ""
}
}
Note: the "readonly": true and "visible": "" options in the Prompt UI for the currencyTable and currencyCode controls. If the Hide control at runtime option was selected, then the option would be "visible": false.
Here is the Program code for the currency conversion custom step:
/* Currency Exchange from RapidAPI */
/* For more information and to sign up for an API Key: https://rapidapi.com/fyhao/api/currency-exchange/details */
/* Note: This free API may not support all currencies on the SAS_CURRENCY list */
/* Currency Conversion API endpoint */
%let _APIendpoint=https://currency-exchange.p.rapidapi.com ;
/* Make call to API to get current Exchange Rate */
filename _curRate temp ;
proc http
method=get
url="&_APIendpoint/exchange"
query= ("from"="&convertFrom"
"to"="&convertTo"
"q"="1")
out=_curRate ;
headers
"X-Rapidapi-Key"="&_apiKey"
"X-Rapidapi-Host"="currency-exchange.p.rapidapi.com" ;
run ;
/* Write the exchangeRate to a Macro */
data _null_ ;
infile _curRate ;
input rate ;
/* Avoid notes about data type conversions */
call symput('exchangeRate',strip(put(rate,32.12))) ;
run ;
%put ***Exchange Rate of 1 &convertFrom to &convertTo is &exchangeRate ;
/* Calculate the converted rate for the selected column */
data &outTable ;
set &inTable ;
&convertedCol_name=round((¤cyCol_1_name * &exchangeRate),0.01) ;
run ;
I have now decided that I want to edit the custom step and hide the Currency reference table and Currency code column selections from the user. To do this I edit their respective properties and check the Hide control at runtime option.
When I select Preview the Currency reference table and Currency code column are not displayed, but the Convert currency from and Convert currency to drop-down columns contain the list of CURRENCY_CD as their selections.
I designed my custom step, so now it is time to test it in Stand-alone mode. Here I am converting the ACTUAL column on the SASHELP.PRDSALE table from USD to EUR.
Next, I test the custom step in a SAS Studio Flow. Here I am converting the ACTUAL column on the SASHELP.PRDSALE table from CAD to AUD.
Summary
You now have the option to hardcode the values for an input table and/or column in your custom step giving you the ability to use reference table information for drop-down list selections in your custom steps. You also have the option to show or hide these hardcoded values to users. For more information, please refer the documentation.
Stay tuned for my next blog on using this new hardcoded value feature with the new SASDQREF library tables for the Quality Knowledge Base (QKB) metadata.
Find more articles from SAS Global Enablement and Learning here.
