As of the stable 2024.04 SAS Viya release, you can specify whether the values for the column selector control in a custom step come from an input table or from another column selector control.   Here is the new option:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   In this example custom step, the first column selector has its selections tied to an input table.     The second column selector is tied to the first column selector which means its available values for selection will come from the values selected in the first column selector.     Note:  The Link to column selector option lists the column selector controls in the step that are linked to input tables. This list does not include column selectors that are linked to another column selector control. In other words, the second column selector will not show as possible link to another column selector.   I also used a new style feature available for custom step controls to indent the second column selector to make it more visually obvious that is related to the first column selector.     When I preview the custom step, I can select certain columns for the first column selector control.     Once I have selected those four columns for the first column selector control, I can only select from those for the second column selector control.     Summary   This is an enhancement to the column selector control that you can take advantage of in your custom step design.  For more information on the Column Selector control in a custom step, refer to the following: Column Selector Control documentation SAS Studio Custom Steps - Coding Made Easier Using the Column Selector Control blog SAS Viya: Using Cascading Prompts in a Custom Step blog. ... View more

As of the Viya stable 2024.01 release, there is a Link control for Custom Steps. Use the Link control to add hyperlinks to the user interface of your custom step design. For security reasons, this feature must be enabled by a SAS administrator in SAS Environment Manager on the Configuration page before it can be added to a custom step.   Enable Link Control for Custom Step   A SAS administrator must log on to SAS Environment Manager to configure the use of the Link control in a Custom Step.  On the Configuration Page, search for flow.  Select the Data Flow service.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   For sas.data.flows section, select  to edit the settings for the allowedStepLinks option.     Add supported domains (e.g. https://, file://, vscode://, etc.) in the format of a regular expression (regex) and use the OR character (|) in a regular expression to separate multiple domains.  Note: JavaScript is not supported.  I enter the following regular expression and select Save.   ^https?:\/\/(?:.+\.)?sas\.com(?::\d+)?(?:\/.*)?|^vscode:\/\/file\/C:\\.+     This allows links to be added in Custom Steps for https:// for various sas.com sites OR files to open in VSCode that are only located on the C:\ drive.   For help with regular expressions, you can visit the site https://regexlearn.com/.   Use Link Control in a Custom Step   In the Custom Step Designer, there is now a Link control.     I add the Link control to my custom step canvas and enter the link: https://learn.sas.com.     I select Preview and the link is displayed since it matches one of the regex patterns entered in the configuration.     I select the link and website for the entered link is opened in the browser.     I now change the link to: https://google.com.     I select Preview and a message is displayed that link does not match one of the regex patterns entered in the configuration.     I now change the link to: vscode://file/C:\PSGEL315-sas-viya-sas-studio-flow-and-custom-steps\README.md     I select Preview and the link is displayed since it matches one of the regex patterns entered in the configuration.     I select the link and specified file is opened in VSCode.     Note:  You may have issues with using the link control especially the file:// protocol.  Exposing local system files is a major security concern that browsers handle in their own ways.  You may need to adjust some settings and/or add browser extensions.  Some variables that are out of SAS Studio’s control are:   The browser being used. The version of the browser being used; policies change with every release. The company IT policies (whether company guidelines or via managed software).   Summary   There is now a Link control in Custom Steps that you can use to add hyperlinks to your custom step user interface.  This allows you to link to sites and files that provide more information to aid the user in completing the user interface of your custom step or provide some documentation/background about the custom step.  For more information, refer to the documentation.     Find more articles from SAS Global Enablement and Learning here. ... View more

As of the Viya stable 2023.11 release, you have the option to change the default library for the writing of temporary output tables when executing a flow in SAS Studio.  This new option only applies to SAS Studio Flows.   By default, the WORK library is used but you now have the option to set a different library for these temporary tables.  You can set this option for all SAS Studio flows you create or set it for a single SAS Studio flow.   Set for All SAS Studio Flows   In SAS Studio, select Options → Preferences from the menu.  Select Tables to view the table preference options.  The newly added section is Default Output Libraries.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   The application default is the WORK SAS library.  Select the radio button for Output library location and then select   and navigate to the library you want to set as the new default for SAS Studio Flows.     Click Save to save the default output library selection.  This sets the default output library for all your SAS Studio Flows.   Set for Single SAS Studio Flow   Open the SAS Studio Flow where you want to set a different default output table library option.  On the right-hand side of the flow, select    to open the Properties pane for the flow.     In the Default output library for intermediate tables section, the application default is the WORK SAS library.  Select the radio button for Output library location and then select    and navigate to the library you want to set as the new default for SAS Studio Flows.     Save the flow to save the change to the properties.  This sets the default output library just for this SAS Studio Flow.   Summary You can now change the location where temporary output tables are written when executing a SAS Studio Flow.  For more information, refer to the documentation.   Find more articles from SAS Global Enablement and Learning here. ... View more

As of the Viya stable 2023.10 release there is another option for dependencies between controls – the Enablement dependency option.  Therefore, there are now two dependency options – Visibility and Enablement.  I will review both dependency options in this post.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Visibility versus Enablement   The Visibility dependency option is used to determine whether or not the control is displayed to the end user.  A control that uses the Enablement dependency option is always displayed and the dependency is used to determine whether or not the control is available for editing.   Example   Let’s look at a working example.  I downloaded the GeoDistance with Rounding custom step from the GitHub - sassoftware/sas-studio-custom-steps: Repository to share SAS Studio Custom Steps to illustrate the dependencies options.  For more information on how to do this, view this YouTube video.   I open the custom step in Designer mode and look at its Options tab to review the dependency options.     Visibility   Currently, the Decimal places text or numeric control has the dependency for Visibility set.  If the Round checkbox control is checked, then the Decimal places text or numeric control will be displayed; otherwise, the Decimal places text or numeric control will not be displayed.  For more information on the expression syntax for dependencies, view its documentation.     Let’s preview this.  With the Round checkbox control unchecked, the Decimal places text or numeric control is not displayed.     When the Round checkbox control is checked, the Decimal places text or numeric control is displayed.     Enablement   Now, let’s change so the dependency for the Decimal places text or numeric control uses the Enablement option instead.     When the Round checkbox control is unchecked, the Decimal places text or numeric  control is displayed, but it is read-only (i.e., not enabled).     When the Round checkbox control is checked, the Decimal places text or numeric is displayed and able to be edited (i.e., enabled).     Summary   The new dependency feature, Enablement, gives you more design possibilities for your custom steps.   For more information on the new Enablement dependency review its documentation.     Find more articles from SAS Global Enablement and Learning here. ... View more

In a previous article, I demonstrated how you can use a SAS Quality Knowledge Base (QKB) reference table in a custom step. Those reference tables are based on an out-of-the-box QKB. What if you have a customized QKB? In this blog, I show how you can create the QKB definition reference tables for your customized QKB. Then, you could use those custom QKB reference tables in custom steps or in the SAS-delivered QKB steps (e.g., Match Codes).     The high-level steps to create the data quality reference library for a customized QKB are: Add the Customized QKB to SAS Compute as Default QKB Create a Folder on the SAS Server for the Reference Tables Run the DQ - Create QKB Reference Tables Custom Step Add the Created QKB Reference Tables Location to SAS Studio AUTOEXEC Add the Customized QKB to SAS Compute as Default QKB The DQ - Create QKB Reference Tables custom step analyzes the default QKB in SAS Compute to create the QKB reference tables. Therefore, follow the steps in this article to set your customized QKB as the default for SAS Compute.     Create a Folder on the SAS Server for the Reference Tables Next, you need to create a folder on the SAS Server to save the output data sets for the QKB reference tables. In SAS Studio, on the Explorer tab, navigate to the SAS Server location where you want to create the folder. Right-click and select New folder. Note: You should be logged in as a SAS Administrator to perform this function.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Enter the name of the folder you want to create and select OK.     Make note of the location of the SAS Server folder; you will need it when you run the custom step to create the QKB reference tables.     Run the Create QKB Reference Tables Custom Step The DQ - Create QKB Reference Tables custom step is located here. Select    to download the .step file.     In SAS Studio log on as a SAS Administrator. Navigate to the Explorer tab, upload the DQ - Create QKB Reference Tables.step to a SAS Content folder.   Open the custom step in its own tab.     Enter the desired name for the SAS Library and select the SAS Server path you created for the output tables.     Run the custom step and view its results. The specified SAS library is created with the QKB reference tables for each of the definition types. In my customized QKB there is a parse definition at the English language level called Clothing and it has the tokens of: Department, Item, Color, and Size.     Add the Created QKB Reference Tables Location to SAS Studio AUTOEXEC After running this custom step, you may want to consider adding libname SASDQREF "path-containing-your-QKB-Reference-Tables"; to your autoexec in SAS Studio or SAS Environment Manager. This results in the Data Quality steps that use the QKB to utilize your newly created reference tables. I am going to do this in SAS Environment Manager, so it is available for all SAS Studio users.   In SAS Environment Manager on the Configuration page, select Compute Service. Edit the sas.compute.server: autoexec_code section. Note: You should be logged in as a SAS Administrator to perform this function.     In the Contents area enter the following:   /* Custom QKB Reference Tables */ libname SASDQREF "/qkb/reference/path" ;   Log out of all SAS Compute sessions. Must re-open compute session for change to take effect. I sign-out and close my browser with Viya. I re-open and sign back into my Viya installation. The SASDQREF Library with my custom QKB reference files should now be listed anytime a new SAS Compute session is started.     Now, any steps that use the QKB (e.g., Match Codes) now reference my customized QKB reference tables. As shown in the screen shot below, the Match Codes step allows me to select my custom match code definition of Clothing.     Summary The DQ - Create QKB Reference Tables custom step analyzes the default QKB in SAS Compute to create the QKB reference tables. You can then use these reference tables based on your customized QKB in the supplied QKB steps or in your own custom steps. For more information, please refer to this blog.   Acknowledgements Thanks to Charles Norman, Brant Kay, and Nicolas Robert for their assistance with the Program code for this custom step.   Find more articles from SAS Global Enablement and Learning here. ... View more

SAS Studio Custom Steps enable you to create a user interface for users at your site to complete a specific custom task. There is a public GitHub repository of these custom steps designed by both SAS employees and other customers that you can import and use at your site. You can use them as designed or tweak them to fit your specific use case. In this presentation, you will learn how to access this public GitHub repository of custom steps and import them into your environment. This demonstration will use two custom steps from the public GitHub repository in a SAS Studio Flow that anonymizes a column in the input data, creates a new output table, and then sends an email that the output is ready for viewing. ... View more

In SAS Viya, you can use the SAS Quality Knowledge Base (QKB) to perform data quality operations (e.g., parse and standardize) on your data either in SAS Data Studio (CAS) or in SAS Studio (CAS and/or SAS Compute).  As an Administrator, you can add a customized QKB to your SAS Viya installation.   Note:  To learn how to customize a QKB, you can take the Understanding the SAS Quality Knowledge Base VLE course. The QKB Customization Tool can be downloaded from here.   The high-level steps to add a customized QKB in SAS Viya for CAS and SAS Compute are:   Create a QARC (QKB Archive) File for the Customized QKB Add Customized QKB QARC File to CAS Add Customized QKB QARC File to SAS Compute   Create a QARC File (QKB Archive) for the Customized QKB   A QKB must be in the QARC (QKB Archive) format to be imported to a CAS Server and/or SAS Compute in SAS Viya.  A command-line QARC tool is provided to convert your QKB to this format.  The tool to create the QARC file is available for download here.   I downloaded the Windows version of QARC program since the tool used to Customize a QKB is Windows-based, so it is easier to run this process there.   Select any image to see a larger version. Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.   The tool needs to be extracted.  I extract the program to my C:\Customized QKBs directory.     To create a QARC file, first locate the directory of your customized QKB.  In my case it is located at: C:\Customized QKBs\CI33_customized.     Once you have located the directory that contains your customized QKB, then from a Windows command prompt run the following code:    Cd \path\to\qarc.exe qarc create \path\to\qkb myqkb.qarc   In my case I submit the following commands.    cd "C:\Customized QKBs" qarc create "C:\Customized QKBs\CI33_customized" CI33_customized.qarc   Note:  It may take about 10 – 20 minutes to create the QARC file depending on the size of your customized QKB.     The QARC file has been created and is ready to be imported to CAS.   Add Customized QKB QARC File to CAS   Note:  In order to import the QKB file using the user interface, the QARC file must be in a location accessible from your local machine.     Next, log on to SAS Environment Manager as an Administrator to the Viya installation where you want to add the customized QKB.  You must have SAS Administrator privileges to import a QKB QARC file into SAS Viya.  When signing in, select Yes to assume the group SASAdministrators, so you have the appropriate privileges.     Select Manage Environment to access SAS Environment Manager, then select the Quality Knowledge Bases tab.     To import a QARC file, select  and navigate to where the QARC file for the customized QKB is located by using the Browse button. Enter the target name and server for the QKB.  Select Import to import the file.     Note:  It may take about 5 – 10 minutes to import the QARC file.   Once the import is complete, confirm that the imported QKB is now listed.       Set Customized QKB as Default QKB in CAS   After importing the QKB, to designate the newly imported QKB as the default QKB for your CAS server, add (or change) the configuration settings for the CAS server.   In SAS Environment Manager on the Configuration page, select the name of your CAS server. By default, this is cas-shared-default. Edit the sas.cas.instance.config: config section.      In the Contents area enter the following:     cas.DQSETUPLOC="DefaultQKBName" cas.DQLOCALE="DefaultLocale"   Note:  The DefaultQKBName must match the name found on the Quality Knowledge Bases page of SAS Environment Manager.  The DefaultLocale is the 5-character QKB locale abbreviation.  For example, ENUSA = English-United States and ESESP = Spanish-Spain. SAS Quality Knowledge Base for Contact Information 33 - QKB Locale ISO Codes   Below are my modified config settings to set my imported Customized QKB as the new default QKB.     After editing this configuration setting, you need to restart the sas-data-quality pod for the change to take effect by issuing the following commands:     kubectl -n name-of-namespace delete pods -l app.kubernetes.io/managed-by=sas-cas-operator kubectl -n name-of-namespace get pods | grep sas-data-quality kubectl -n name-of-namespace delete pod sas-data-quality-pod-name   Note:  In the last command, replace sas-data-quality-pod-name with the name of the pod that is returned by the second command. This enables the automatic import of the QKB that is specified by the DQSETUPLOC server option.   Here is what I submitted to restart the sas-data-quality pod for my SAS Viya installation.  Note: My namespace is gelenv.     Finally, after waiting a few minutes for the sas-data-quality pod to restart, return to the Quality Knowledge Bases tab in SAS Environment Manager and refresh the display to confirm that the Customized QKB is now set as the default QKB.     Note: It will take a few minutes for the pod to restart and reflect this change.  It will display a message “No QKBs were found” until the pod is fully restarted.   Add Customized QKB QARC File in SAS Compute   To add the customized QKB to SAS Compute, upload its .qarc file to a location on your SAS Server. I uploaded mine here: /shared/gelcontent/Customized_QKBs/.     Next, you need to find the location of the QKB bash scripts for your SAS Viya installation by issuing the following command.    find . -name containerize-qkb.sh     In my case the QKB bash scripts are located here: ./project/deploy/gelenv/sas-bases/examples/data-quality/scripts/   Next, change directory to that location:     Then, run the following command to containerize your customized QKB:   bash containerize-qkb.sh "My Customized QKB" /path/to/myqkb.qarc registry.mycompany.com/mycustomizedqkb:v1   Note: registry name must be in lowercase with no spaces.     Note: It may take a few minutes for this script to complete.   Next, run the following command to deploy the customized QKB.   bash deploy-qkb.sh registry.mycompany.com/mycustomizedqkb:v1 | kubectl apply -n my-namespace -f –       Then run this command to create the sas-quality-knowledge-base-list-job.   bash list-qkbs.sh | kubectl apply -n my-namespace -f –     Then run the following command to get a list of registered QKBs for SAS Compute:   kubectl -n my-namespace logs job.batch/sas-quality-knowledge-base-list-job-nameReturnedFromPreviousCommand     All registered QKBs in SAS Compute are displayed including the customized one you just registered. Finally, run the following command to delete the sas-quality-knowledge-base-list-job you just created.   kubectl -n my-namespace delete job.batch/sas-quality-knowledge-base-list-job-nameReturnedFromPreviousCommand       Set Default QKB in SAS Compute   In SAS Environment Manager on the Configuration page, select Compute service. Edit the sas.compute.server: configuration_options section.        In the Contents area enter the following:   -DQSETUPLOC "DefaultQKBName" -DQLOCALE (DefaultLocale)   Note:  The DefaultQKBName must match the name of the customized QKB returned from the listing of registered QKBs for SAS Compute.  The DefaultLocale is the 5-character QKB locale abbreviation.  For example, ENUSA = English-United States and ESESP = Spanish-Spain. SAS Quality Knowledge Base for Contact Information 33 - QKB Locale ISO Codes     Log out of all SAS Compute sessions. Must re-open compute session for change to take effect.  I sign-out and close my browser with Viya.  I re-open and sign back into my Viya installation.   In SAS Studio, test with a definition in the default locale specified for your default customized QKB to confirm customized QKB is being used.   A custom parse definition in my customized QKB is Clothing.      data _null_; version=DQVERQKB(); token_names=DQPARSEINFOGET('Clothing'); put version; put token_names; run;     I have now confirmed that SAS Compute is using my customized QKB and default locale setting. Refer to the SAS Data Quality Language Reference section in the SAS Data Quality guide other code examples you can test with.   Summary You can use your customized SAS Quality Knowledge Base (QKB) in both CAS and SAS Compute in SAS Viya to perform data quality operations.  Refer to the QKB Management section in the SAS Data Quality guide for more information.     Find more articles from SAS Global Enablement and Learning here. ... View more

In my previous post, I reviewed how to use the Make control read-only and hide the control at runtime options for referencing table information to populate a drop-down list selector in a Custom Step. In this post, I will cover how to use those new options with the new SAS Quality Knowledge Base (QKB) reference tables in a Custom Step, so you can include the QKB locale and definition selections in your Custom Step.     As of the 2023.06 release, there is a new SAS library called SASDQREF.  It contains data quality reference tables; the majority of which contain the metadata from the SAS Quality Knowledge Base (QKB).  The tables are located on the SAS Server at: /opt/sas/viya/home/commonfiles/home/share/DMData.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   CONTACTS – listing of contact records that can be used to demonstrate data quality functions.  This is the same data as the CONTACTS table delivered with Data Management Studio. DM_CADEF - listing of available casing definitions in the QKB DM_DEF - listing of all definitions in the QKB DM_EXDEF - listing of extraction definitions in the QKB DM_GNDEF - listing of gender analysis definitions in the QKB DM_IDDEF - listing of identification analysis definitions in the QKB DM_LGDEF - listing of locale guessing definitions in the QKB DM_LOC - listing of all locales in the QKB DM_MADEF - listing of all match code definitions in the QKB DM_MKDEF - listing of all data masking definitions in the QKB.  This is a subset of the standardization definitions in the QKB and are used to anonymize and mask data. DM_PADEF - listing of all parsing definitions in the QKB DM_PTDEF - listing of all pattern definitions in the QKB DM_STDEF - listing of all standardization definitions in the QKB   To demonstrate how these tables can be used I am going to review how I can now update my Anonymize and Mask Data Custom Step from a prior post.  (Note:  The user interface for creating custom steps has had improvements since I wrote that post.)    In that post when I built that version of the Anonymize and Mask Data Custom Step, I manually entered the data masking standardization definitions in the drop-down list control and hard-coded QKB locale to ENUSA (English – United States) in the Program (Template) code.  In this post, I will show how I can use the DM_MKDEF table that lists all the data masking definitions in the QKB to populate the drop-down lists for the data masking definitions as well as one for the QKB locale.    Below is an excerpt from the table.  As part of my custom step, I will be referencing the columns highlighted in yellow.       To start my revised Anonymize and Mask Data Custom Step, I add the controls for an input and output table.     Next, I add another input table control.  This one I use to hard-code its table to the DM_MKDEF table in the SASDQREF library.  I set its properties to read-only and hide control at runtime so the end user of the custom step will not see this control.     I add a column selector control and hard-code it to the Locale description column (DM_DESC) on the DM_MKDEF table. I set its properties to read-only and hide control at runtime so the end user of the custom step will not see this control.     I add a column selector control and hard-code it to the data masking definition column (DM_NAME) on the DM_MKDEF table. I set its properties to read-only and hide control at runtime so the end user of the custom step will not see this control.     Next, I add a column selector control to allow the user to select the column to mask from the input table they select.     I add a drop-down control for the QKB locale selection and make it a dynamic list based on the Locale column selector I added previously.     I add a drop-down control for the data masking definition selection and make it a dynamic list based on the Masking definition column selector I added previously.     Next, I create a prompt hierarchy between the two drop-down fields.  The available masking definitions to select are filtered by the QKB locale selection.  For more information on prompt hierarchies, refer to this post.     I had a new column control for the new masked column and give it a default name of maskedColumn; however, the end user can change that name if desired.     For the last part of my custom step design, I drop the column to be masked from the output port and add the new masked column.     To complete my custom step, I add the code to be executed on the Program tab.  As part of the code, I return the five-character QKB Locale that is associated the description of the locale that the end user selected.  Then, that locale is loaded and the dqStandardize function is used to apply the selected masking definition for that locale to return the masked column.   /* Create SASDQREF library in case it doesn't exist in the context where this custom step is being executed */ %macro checkSASDQREF ; %if %sysfunc(libref(SASDQREF)) %then %do ; %if %sysfunc(libname(SASDQREF,!sasroot/../commonfiles/home/share/DMData)) %then %put %sysfunc(sysmsg()) ; %else %put NOTE: SASDQREF library assigned to %sysfunc(pathname(SASDQREF)). ; %end ; %else %put NOTE: SASDQREF library exists. ; %mend checkSASDQREF ; %checkSASDQREF ; /* Resolve selectedLocale to its 5-character QKBLocale */ proc sql ; select distinct DM_LOCALE into :QKBLocale from SASDQREF.DM_MKDEF where DM_DESC="&selectedLocale"; run ; %put &=QKBLocale.; /* Load Selected QKB Locale */ %DQLOAD(DQLOCALE=(&QKBlocale)); /* Apply Data Masking Standardization Definition */ data &outTable (drop=&incolumn_1_name); /* also specify the drop column in the output table metadata */ set &inTable; /* also specify the new column name in the output table metadata */ &maskedColumn_name=dqStandardize(&inColumn_1_name,"&selectedDefinition", "&QKBlocale"); run;   Now I can test my revised Anonymize and Mask Data custom step.  I select an input table and select to mask the CONTACT column.  I also select to use the English (United States) QKB locale and its Mask All Characters Except First and Last masking definition.  The maskedColumn  I rename to CONTACTmasked on the output table.     Note:  The controls that I hard-coded are hidden from the end user since I selected to hide those controls at runtime.     Summary   As of the 2023.06 release, there is a new SAS library called SASDQREF.  It contains data quality reference tables; the majority of which contain the metadata from the SAS Quality Knowledge Base (QKB).  These can help you build custom steps where you want to use definitions from the QKB.   The revised Anonymize and Mask Data custom step that I built as part of this post can be downloaded from here.        Find more articles from SAS Global Enablement and Learning here. ... View more

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((&currencyCol_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. ... View more

In a previous blog, I reviewed using PROC EEL in SAS Studio powered by SAS Viya.  In this four-part blog series, I cover examples of the three data monitoring rule types (row, group, and set) and an example with multiple rules by using PROC EEL in both SAS code and Custom Steps in SAS Studio. Row-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step Group-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step Set-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step Multiple Row-based Data Monitoring Rules using PROC EEL in SAS Code and a Custom Step (this blog)   Multi-Rule Monitoring using PROC EEL For this demonstration on using PROC EEL for multi-rule I am going to check some columns on a table for missing values.   Below is the Contact List with Email input table, I use for my rule check.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Here is the PROC EEL code that builds the rule table and performs the check for the Address, Zip, Phone, and Email columns for missing values.  Note:  I specified the primary key column for the input table using the inputkey option.     Here are the results when I run this code against the input table.  The output is written to the table called MISSING as specified in the code.     The table lists the primary key (ID) and the rule_name that was violated for that record.  A record could fail more than rule check (e.g., IDs 3 and 6).   Multi-Rule Monitoring in a Custom Step Instead of hard-coding the input table, the primary key column, the columns to check for blanks, and the output table, I decided to create a custom step where the user can select that information.  For more details on creating custom steps refer to the following: SAS Demo - Create a Custom Step in SAS Studio on SAS Viya (YouTube) My First SAS Studio Flow Custom Step | Global Enablement & Learning Blog SAS Documentation on Custom Steps in SAS Studio. Creating the Custom Step On the main page of my custom step, I add controls for the selection of the following: Input table Primary key column Output table.   Note:  The columns for the rule table are determined in the Program code for the custom step.  It will create a rule to check all columns except the primary key column for missing values (blanks).   Below is the JSON for the Prompt UI design for this custom step.   { "showPageContentOnly": true, "pages": [ { "id": "page1", "type": "page", "label": "Multiple Rules - Check all fields for missing values", "children": [ { "id": "inTable", "type": "inputtable", "label": "Select the input table", "required": true, "placeholder": "", "visible": "" }, { "id": "primaryKey", "type": "columnselector", "label": "Select the primay key field", "order": false, "columntype": "a", "max": 1, "min": 1, "visible": "", "table": "inTable" }, { "id": "outTable", "type": "outputtable", "label": "Select the output table", "required": true, "placeholder": "", "visible": "" } ] }, { "id": "page2", "type": "page", "label": "About", "children": [ { "id": "aboutText", "type": "text", "text": "This custom step performs multiple rule checks. It checks every column of the selected input table (except for the primary column) for blank values.\n\n\nVersion 1.0 (12APR2023)", "visible": "" } ] } ], "values": { "inTable": { "library": "", "table": "" }, "primaryKey": [], "outTable": { "library": "", "table": "" } } }   Here is the Program code to perform the row rule check using PROC EEL.  Note: It is similar to the code in my above examples but uses the functions FieldCount and FieldName from the expression language to retrieve the field count and field name values for the columns on the input table.   /* Multiple Row-rules for missing information */ /* Build Rules File to check for missing data from all fields from the input table except the one indicated as the primary key */ proc eel data=&inTable out=ruledata (keep=rule_name rule); submit ; pre: string(255) rule_name string(32767) rule /* Loop thru to get all the fields from the input table */ hidden integer i for i = 1 to FieldCount() begin if FieldName(i)!=getvar("primaryKey_1_name") begin rule_name=FieldName(i) & " MISSING" rule=FieldName(i) &"==''" pushrow() end end main: /* do not return any row info from the input table */ return false endsubmit ; run ; proc eel data=&inTable rules=ruledata out=&outTable inputkey=&primaryKey ; quit ;   Running the Custom Step   When I run the custom step, I use the same input table as my example above and select the primary key column as ID and set the output table.     The RULEDATA table is created with a rule check for MISSING for each column of the input table except the specified primary key column.     My results are the same with the exception that I have now run the check for all columns on the table except the primary key column.     Since I have set this as a custom step where the inputs for the set rule check are generic, I can easily change the table to check for missing values.   I have changed the input table to ORDERS and its primary key field is ORDER_ID.     In this case, the RULEDATA table now has the rule checks based on this input table’s columns.     And the MISSING_CHECK table has its results.     No rows were written to the output table indicating that none of the columns on the specified input table (ORDERS) are missing any values. Summary In conclusion, you can use PROC EEL combined with Custom Steps to perform multi-rule monitoring of your data where the inputs for the rule check are not hard-coded into the program.  For more information on Expression Engine Language coding, please refer to the following: PROC EEL Statement documentation Expression Language: Reference Guide Using PROC EEL video by Arnold Toporowski   Acknowledgements Special thanks to: Mickey Schauf for providing the inspiration to create this blog series Andrew Shakinovsky for taking the time to let me talk through my ideas for this blog series enabling me to confirm that the concept was possible!   Find more articles from SAS Global Enablement and Learning here. ... View more

In a previous blog, I reviewed using PROC EEL in SAS Studio powered by SAS Viya.  In this four-part blog series, I cover examples of the three data monitoring rule types (row, group, and set) and an example with multiple rules by using PROC EEL in both SAS code and Custom Steps in SAS Studio. Row-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step Group-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step Set-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step (this blog) Multiple Row-based Data Monitoring Rules using PROC EEL in SAS Code and a Custom Step   Set-based Monitoring Rule using PROC EEL   For this demonstration on using PROC EEL to mimic the behavior of a set-based business rule in DataFlux Business Rule Manager, I will use the example of checking the missing percentage for a column on an input table.  If that column as a missing percentage greater than 0%, then the rule is triggered.   Below is the Contact List with Email input table, I use for my rule check.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Here is the PROC EEL code to perform the check if the missing percentage is greater than 0% for the Email column.   proc eel data=SASDM.contact_list_with_email out=SASDM.MISSING_CHECK (keep=Rule_Result Missing_Pct); submit ; pre: hidden real total_records total_records=0 hidden real total_missing total_missing=0 //Set Target Percentage real i i = 0.00 main: total_records=total_records+1 if email=='' total_missing=total_missing+1 return false post: string(6) Rule_Result real Missing_Pct Missing_Pct=round(((total_missing/total_records) * 100),2) //write log messages logmessage("Total number of records " & total_records & " and total missing is " & total_missing) logmessage("Missing percentage is: " & Missing_Pct) //Evaluate Set if Missing_Pct > i Rule_Result='FAILED' else Rule_Result='PASSED' pushrow() endsubmit ; run ;   Make note of the following in the code:   keep option on the proc eel statement to specify the column names to keep in the output return false statement in the main section to prevent the individual rows from the input from being returned in the output pushrow() statement in the post section to return a summary row to the output   Here are the results when I run this code against the input table.     The email column is missing values in 5.45% of the input data set so it failed the check.   Set-based Monitoring Rule in a Custom Step   Instead of hard-coding the input table, the column to check for blanks, the missing percentage to trigger the rule, and the output table, I decided to create a custom step where the user can select that information.  For more details on creating custom steps refer to the following:   SAS Demo - Create a Custom Step in SAS Studio on SAS Viya (YouTube) My First SAS Studio Flow Custom Step SAS Documentation on Custom Steps in SAS Studio.   Creating the Custom Step   On the main page of my custom step, I add controls for the selection of the following:   Input table Column to check for blanks Threshold for percentage missing Output table.   Below is the JSON for the Prompt UI design for this custom step.   { "showPageContentOnly": true, "pages": [ { "id": "page1", "type": "page", "label": "Set Rule Check for Percent Missing from Column", "children": [ { "id": "inTable", "type": "inputtable", "label": "Select the input table", "required": true, "placeholder": "", "visible": "" }, { "id": "ruleCheckCol", "type": "columnselector", "label": "Select the column to check for missing values", "order": false, "columntype": "a", "max": 1, "min": 1, "visible": "", "table": "inTable" }, { "id": "pctMissing", "type": "numstepper", "label": "Select the acceptable percent missing for the specified column in the input table", "required": true, "integer": false, "min": 0, "max": 100, "stepsize": 1 }, { "id": "outTable", "type": "outputtable", "label": "Select the output table", "required": true, "placeholder": "", "visible": "" } ] }, { "id": "page2", "type": "page", "label": "About", "children": [ { "id": "aboutText", "type": "text", "text": "This custom step performs a set-based rule check to monitor whether or not a column in the input table exceeds a specified percentage for missing values.\n\n\nVersion 1.0 (12APR2023)", "visible": "" } ] } ], "values": { "inTable": { "library": "", "table": "" }, "ruleCheckCol": [], "pctMissing": 0, "outTable": { "library": "", "table": "" } } }   Here is the Program code to perform the row rule check using PROC EEL.  Note: It is similar to the code in my above examples but uses the functions VarEval and GetVar from the expression language to retrieve the macro variable values for the selected columns.   /* Performa a Set Rule for missing percentage */ proc eel data=&inTable out=&outTable (keep=Rule_Result Missing_PCT); submit ; pre: hidden real total_records total_records=0 hidden real total_missing total_missing=0 //Set Target Percentage real i i = getvar("pctMissing") main: total_records=total_records+1 if VarEval(getvar("ruleCheckCol_1_name"))=='' total_missing=total_missing+1 return false post: string(6) Rule_Result real Missing_Pct Missing_Pct=round(((total_missing/total_records) * 100),2) //write log messages logmessage("Total number of records " & total_records & " and total missing is " & total_missing) logmessage("Missing percentage is: " & Missing_Pct) //Evaluate Set if Missing_Pct > i Rule_Result='FAILED' else Rule_Result='PASSED' pushrow() endsubmit ; run ; Running the Custom Step   When I run the custom step, I use the same input table as my example above and map the Email column as the field to monitor for blanks and set the percentage missing threshold to 0.     My results are the same – the rule is violated, and the missing percentage is 5.45 for the Email column.     Since I have set this as a custom step where the inputs for the set rule check are generic, I can easily change the field to check for missing values to Phone and acceptable missing percentage to 10.     In this case, the set of data also fails the check with 61.82% of the Phone records missing values.     Summary   In conclusion, you can use PROC EEL combined with Custom Steps to perform set-based rule monitoring of your data where the inputs for the rule check are not hard-coded into the program.  For more information on Expression Engine Language coding, please refer to the following:   PROC EEL Statement documentation Expression Language: Reference Guide Using PROC EEL video by Arnold Toporowski   Acknowledgements Special thanks to: Mickey Schauf for providing the inspiration to create this blog series Andrew Shakinovsky for taking the time to let me talk through my ideas for this blog series enabling me to confirm that the concept was possible!   Find more articles from SAS Global Enablement and Learning here. ... View more

In a previous post, I reviewed using PROC EEL in SAS Studio powered by SAS Viya.  In this four-part series, I cover examples of the three data monitoring rule types (row, group, and set) and an example with multiple rules by using PROC EEL in both SAS code and Custom Steps in SAS Studio.   Row-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step Group-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step (this post)  Set-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step Multiple Row-based Data Monitoring Rules using PROC EEL in SAS Code and a Custom Step   Group-based Monitoring Rule using PROC EEL   For this demonstration on using PROC EEL to mimic the behavior of a group-based business rule in DataFlux Business Rule Manager, I will use the example of monitoring for employees who exceed their sales target.  In my example, if an employee has an order total of more than 50000 on the supplied input set, then they have exceeded their sales target and are eligible for a bonus. (Sometimes data monitoring can be positive!)   Below is the Orders input table, I use for my rule check.   Select any image to see a larger version. Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.   Here is the PROC EEL code to perform the check on whether the employee exceeded their sales target and therefore should receive a bonus.  Note:  For a grouping in EEL, the input data must be sorted by the column(s) you are grouping.  In my case, I am grouping by employee_id since I need to add up the order totals for each employee.   /* Input data must be sorted prior to performing grouping in EEL */ proc sort data=SASDM.ORDERS out=input_sorted ; by employee_id ; proc eel data=input_sorted out=SASDM.EMPLOYEES_EXCEEDED_TARGET (keep=employee_id total_order_amount); grouping employee_id ; submit ; pregroup: integer total_order_amount total_order_amount=0 main: total_order_amount=total_order_amount+order_total return false postgroup: logmessage("Total order amount for Employee id " & employee_id & " is " & total_order_amount) // check for exceeding target order amount if total_order_amount > 50000 pushrow() endsubmit ; run ; /* Delete temporary sorted table */ proc datasets library=work ; delete input_sorted ; run ;   Make note of the following in the code: keep option on the proc eel statement to specify the column names to keep in the output return false statement in the main section to prevent the individual rows from the input from being returned in the output pushrow() statement in the postgroup section to return a summary row to the output if the target amount of 50000 is exceeded   Here are the results when I run this code against the input table.     Employees 3 and 9 have exceeded the target of 50,000 and should receive a bonus.     Group-based Monitoring Rule in a Custom Step   Instead of hard-coding the input table, the employee_id column, order_amount column, the sales target, and the output table, I decided to create a custom step where the user can select all that information.  For more details on creating custom steps refer to the following: SAS Demo - Create a Custom Step in SAS Studio on SAS Viya (YouTube) My First SAS Studio Flow Custom Step | Global Enablement & Learning Blog SAS Documentation on Custom Steps in SAS Studio.   Creating the Custom Step   On the main page of my custom step, I add controls for the selection of the following: Input table Column selector for employee_id field Column select for sales_amount field Sales target amount Output table. In my design, I set the target total sales amount to a default of 50,000, but that can be changed by the user when they use the custom step.     Below is the JSON for the Prompt UI design for this custom step.   { "showPageContentOnly": true, "pages": [ { "id": "page1", "type": "page", "label": "Grouping Rule to Find Employees Who Exceeded Their Target", "children": [ { "id": "inTable", "type": "inputtable", "label": "Select the input table", "required": true, "placeholder": "", "visible": "" }, { "id": "employee_id", "type": "columnselector", "label": "Select the Employee ID field", "order": false, "columntype": "a", "max": 1, "min": 1, "visible": "", "table": "inTable" }, { "id": "sales_amount", "type": "columnselector", "label": "Select the Sales Amount field", "order": false, "columntype": "a", "max": 1, "min": 1, "visible": "", "table": "inTable" }, { "id": "target", "type": "numstepper", "label": "Specify the total sales target amount", "required": true, "integer": true, "min": null, "max": null, "stepsize": 5000 }, { "id": "outTable", "type": "outputtable", "label": "Select the output table", "required": true, "placeholder": "", "visible": "" } ] }, { "id": "page2", "type": "page", "label": "About", "children": [ { "id": "aboutText", "type": "text", "text": "This custom step performs a group-based rule check to monitor whether or not employees exceeded their specified sales target.\n\n\nVersion 1.0 (12APR2023)", "visible": "" } ] } ], "values": { "inTable": { "library": "", "table": "" }, "employee_id": [], "sales_amount": [], "target": 50000, "outTable": { "library": "", "table": "" } } }   Here is the Program code to perform the row rule check using PROC EEL.  Note: It is similar to the code in my above examples but uses the functions GetVar, SetVar, and FieldValue from the expression language to retrieve the macro variable values for the selected columns.   /* Data needs to be sorted prior to grouping EEL statement */ proc sort data=&inTable out=inTable_sorted ; by &employee_id_1_name ; proc eel data=inTable_sorted out=&outTable (keep=&employee_id_1_name total_order_amount); grouping &employee_id_1_name ; submit ; pre: /* Loop thru to find the selected fields */ hidden integer i begin for i = 1 to FieldCount() begin if FieldName(i)==getvar("employee_id_1_name") setvar("Employee_idx",i) if FieldName(i)==getvar("sales_amount_1_name") setvar("SalesAmt_idx",i) end end hidden integer targetAmount targetAmount = getvar("target") pregroup: integer total_order_amount total_order_amount=0 main: total_order_amount=total_order_amount+FieldValue(getvar("SalesAmt_idx")) return false postgroup: logmessage("Total order amount for " & getvar("employee_id_1_name") & " " & FieldValue(getvar("Employee_idx")) & " is " & total_order_amount) // check for exceeding target order amount if total_order_amount > targetAmount pushrow() endsubmit ; run ; /* Delete temporary sorted table */ proc datasets library=work ; delete inTable_sorted ; run ;   Running the Custom Step   When I run the custom step, I use the same input table as my example above and map the EMPLOYEE_ID column for the employee_id column and the ORDER_TOTAL column for the sales_amount column.  I leave the target total sales amount at the default of 50,000.     My results are the same – Employee IDs 3 and 9 are returned for exceeding the target of 50,000.     Since I have set this as a custom step where the inputs for the check are generic, I can easily change any of the information.  I change the target sales amount to 100,000.     In this case, only Employee ID 3 is returned for exceeding that new sales target.     Summary   In conclusion, you can use PROC EEL combined with Custom Steps to perform group-based rule monitoring of your data where the inputs for the rule check are not hard-coded into the program.  For more information on Expression Engine Language coding, please refer to the following: PROC EEL Statement documentation Expression Language: Reference Guide Using PROC EEL video by Arnold Toporowski   Acknowledgements Special thanks to my colleagues: Mickey Schauf for providing the inspiration to create this series.  Andrew Shakinovsky for taking the time to let me talk through my ideas for this series enabling me to confirm that the concept was possible!   Find more articles from SAS Global Enablement and Learning here. ... View more

In a previous post, I reviewed using PROC EEL in SAS Studio powered by SAS Viya.  In this four-part  series, I cover examples of the three data monitoring rule types (row, group, and set) and an example with multiple rules by using PROC EEL in both SAS code and Custom Steps in SAS Studio. Row-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step (this post) SAS Viya: Group-based Data Monitoring Using PROC EEL in SAS Code and Custom Steps Set-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step Multiple Row-based Data Monitoring Rules using PROC EEL in SAS Code and a Custom Step Row-based Monitoring Rule using PROC EEL   For this demonstration on using PROC EEL to mimic the behavior of a row-based business rule in DataFlux Business Rule Manager, I will use the example where I want to filter (monitor) for rows in a data set where values from two columns are missing. For example, if both the Address and Postal (Zip) Code information are missing from a row in the data set, then that row should be returned.  (I wrote a similar post several years ago using a SAS Intelligent Decisioning rule).   Below is the input table, I use for my rule check.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Here is the PROC EEL code to perform the check on whether both the Address and Zip fields are missing from a row in the input data.  This code returns only the rows where the rule check is violated (i.e., return true).   /* Row-rule to check for 2 fields missing values */ proc eel data=SASDM.CONTACT_LIST_WITH_EMAIL out=test1; submit; if Address=='' and Zip=='' return true return false endsubmit; proc print data = test1 noobs; TITLE "EEL output"; run; run;   Here are the results when I run this code against the input table.     Note only rows 3 and 6 are returned.  They are the only rows in the input table where both Adddress and Zip are blank.   Using Return Status in PROC EEL   This next example is the same as the previous one except for using the return status option.  I use the same input and note the code is the same; however, there is now the addition of returnstatus=RuleCheckFailed as part of the options for the PROC EEL statement.   /* Row-rule to check for 2 fields missing values */ proc eel data=SASDM.CONTACT_LIST_WITH_EMAIL out=test2 returnstatus=RuleCheckFailed; submit; if Address=='' and Zip=='' return true return false endsubmit; proc print data = test2 noobs; TITLE "EEL output"; run; run;   This time the results return every row and flag that rows 3 and 6 violate the row rule check indicated by a 1 in the RuleCheckFailed column which is the name of the return status column in the code.     Row-based Monitoring Rule in a Custom Step   Instead of hard-coding the input table, its two columns to check for blanks, and the output table, I decided to create a custom step where the user can select the input and output tables and the two columns.  For more details on creating custom steps refer to the following: SAS Demo - Create a Custom Step in SAS Studio on SAS Viya (YouTube) My First SAS Studio Flow Custom Step SAS Documentation on Custom Steps in SAS Studio. Creating the Custom Step   On the main page of my custom step, I add controls for the selection of the following: Input table Two columns to check for blanks Output table.     For the Options page, I add a check box control to create a Return Status field.  If that option is checked, then the New Column control is displayed to set the name of the return status column.     Below is the JSON for the Prompt UI design for this custom step.   { "showPageContentOnly": true, "pages": [ { "id": "page1", "type": "page", "label": "Row Rule Check for Both Fields Blank", "children": [ { "id": "inTable", "type": "inputtable", "label": "Select the input table", "required": true, "placeholder": "", "visible": "" }, { "id": "monitorColumns", "type": "columnselector", "label": "Select the two columns to check for blanks", "order": false, "columntype": "a", "max": 2, "min": 2, "visible": "", "table": "inTable" }, { "id": "outTable", "type": "outputtable", "label": "Select the output table", "required": true, "placeholder": "", "visible": "" } ] }, { "id": "page2", "type": "page", "label": "Options", "children": [ { "id": "returnStatus", "type": "checkbox", "label": "Create Return Status Field", "visible": "" }, { "id": "returnStatusField", "type": "newcolumn", "label": "Return Status Field Name", "required": true, "placeholder": "", "hideproperties": false, "readonly": false, "visible": "$returnStatus" } ] }, { "id": "page3", "type": "page", "label": "About", "children": [ { "id": "aboutText", "type": "text", "text": "This custom step performs a row-based rule check to monitor whether or not the two selected fields from the input table are both blank.\n\n\nVersion 1.0 (12APR2023)", "visible": "" } ] } ], "values": { "inTable": { "library": "", "table": "" }, "monitorColumns": [], "outTable": { "library": "", "table": "" }, "returnStatus": false, "returnStatusField": { "value": "RuleCheckFailed", "label": "Return Status Value", "type": "n" } } }   Here is the Program code to perform the row rule check using PROC EEL.  Note: It is similar to the code in my above examples but uses the functions VarEval and GetVar from the expression language to retrieve the macro variable values for the selected columns.   /* Row-rule to check for 2 fields missing values */ /* get returnStatus info */ %let ReturnStatusText= ; %if &returnStatus=1 %then %do ; %let ReturnStatusText=%nrquote(returnstatus=&returnStatusField_name) ; %end ; proc eel data=&inTable out=&outTable &ReturnStatusText ; submit; /* Rule check for both fields missing */ if VarEval(getvar("monitorColumns_1_name"))=='' AND VarEval(getvar("monitorColumns_2_name"))=='' return true return false endsubmit; run;   Running the Custom Step   When I run the custom step, I use the same input table as my examples above and map the Address and Zip columns as the fields to monitor for blanks.     My results are the same – records 3 and 6 violate my rule check.     Next, I add the option for return status.      Again, I get the same results as the return status example above – all records are returned, but records 3 and 6 are flagged for violating my rule check.     However, since I have set this as a custom step where the inputs for the blank fields check are generic, I can easily change the fields that are checked to Phone and Email.  I also remove the option for creating a return status flag.         In this case, there are no violations to my rule check, so my output table is empty.     Summary   In conclusion, you can use PROC EEL combined with Custom Steps to perform row-based rule monitoring of your data where the inputs for the rule check are not hard-coded into the program.  For more information on Expression Engine Language coding, please refer to the following: PROC EEL Statement documentation Expression Language: Reference Guide Using PROC EEL video by Arnold Toporowski Acknowledgements   Special thanks to: Mickey Schauf for providing the inspiration to create this series Andrew Shakinovsky for taking the time to let me talk through my ideas for this series enabling me to confirm that the concept was possible!   Find more articles from SAS Global Enablement and Learning here. ... View more

For people familiar with coding using the DataFlux/SAS Expression Engine Language (EEL) in Data Management Studio or SAS Event Stream Processing (ESP), you can now use it in SAS Studio in SAS Viya as of the 2022.12 (December 2022) stable release.    There are three modes of operation for PROC EEL: Single Expression Mode, Rule Mode, and Discovery Mode.  Something to note is that PROC EEL runs in Compute (not CAS) meaning that if your input table is in CAS the data is brought down to Compute for processing.   Single Expression Mode The single expression mode consists of two types – the simple expression and the grouping expression.   Simple Expression The simple expression can have EEL code in three sections: Pre, Main, and Post.   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:  The numbers represent the order the code is executed.   For this example, my input is below.     The following is the PROC EEL code to indicate that the Country needs standardization if its length is not equal to 3 and it is not in uppercase.       proc eel data=SASDM.country_contacts out=country_stnd ; submit ; pre: string(5) Country_Needs_Standardization main: if len(Country)==3 and Country==upper(Country) Country_Needs_Standardization = "FALSE" else Country_Needs_Standardization = "TRUE" endsubmit ; run ;       Note:  For this example, I did not need to use any post expression code.   Here are the results of running that code against my input.     Grouping Expression The grouping expression can have EEL code in five sections: Pre, Pre-group, Main, Post-group, and Post.  You must also specify the column(s) to group by.   The data must be pre-sorted by those columns(s).       Note:  The numbers represent the order the code is executed.   For this example, I am using the same Country_Contacts input table as I did in the previous example.  However, this time I am grouping the data by Country.  Note the table has already been pre-sorted by Country as is required for PROC EEL grouping.  In the code I am adding up all the ages for each country and writing that total age for each country to a text file.       proc eel data=SASDM.country_contacts ; grouping country ; submit ; pre: file f if fileexists("/tmp/country_age.txt") deletefile("/tmp/country_age.txt") f.open("/tmp/country_age.txt", "w") f.writeline("Country,Total_Age") pregroup: hidden integer total_age total_age=0 main: total_age=total_age+age postgroup: logmessage("Total age for " & country & " is " & total_age) f.writeline(country & "," & total_age) post: f.close() endsubmit ; run ;       Here are the results of running that code against my input.     Rule Mode Rule mode allows you to specify a rule data set to perform multiple rule checks on the same input data set.  The rules table is specified using the RULES argument.  The rules table must contain two columns – for the rule_name and one for the rule itself.  A primary key field must be designated for your input table using the INPUTKEY argument.   For this example, my input is below.     The following is the PROC EEL code to perform multiple rule checks against my input table.       /* Multiple rules for missing information */ data ruledata ; infile datalines dsd delimiter=',' ; input rule_name : $255. rule : $32767. ; datalines; "Address MISSING","Address==''" "Zip MISSING","Zip==''" "Phone and Email MISSING","Phone=='' AND Email==''" ; proc eel data=SASDM.SAMPLE_CONTACTS rules=ruledata out=missing inputkey=ID ; run ;       Below is the resulting rule table:     If a record violates one of the rules its primary key field is written to the output as well as the rule name that was violated.  Note: A single record could violate more than one rule. Here are the results of running that code against my input.      Discovery Mode For discovery mode, no input table is used.  Instead, it analyzes the specified rules table provided in the RULES argument and returns the variables that are needed in an input table to perform the rule checks.   The following is the PROC EEL code to return the columns needed to perform rule checks from the RULEDATA table in the previous example.       /* Multiple rules for missing information */ data ruledata ; infile datalines dsd delimiter=',' ; input rule_name : $255. rule : $32767. ; datalines; "Address MISSING","Address==''" "Zip MISSING","Zip==''" "Phone and Email MISSING","Phone=='' AND Email==''" ; proc eel rules=ruledata out=discovery ; run ;       Here are the results of running that code.     Upcoming Blog Series on using PROC EEL for Data Monitoring For the Data Management Studio users who are familiar with Business Rule Manager and data monitoring, you will recall that a business rule is one of three types: Row, Group, or Set.  Also, those business rules are simply EEL code behind the scenes.     Therefore, in a four-part blog series, I cover examples of those three data monitoring rule types and an example with multiple rules by using PROC EEL in both SAS code and Custom Steps in SAS Studio powered by SAS Viya. Row-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step Group-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step and Upcoming:    Set-based Data Monitoring Rule using PROC EEL in SAS Code and a Custom Step  Multiple Row-based Data Monitoring Rules using PROC EEL in SAS Code and a Custom Step Summary In conclusion, you can now use Expression Engine Language code in SAS Studio powered by SAS Viya by utilizing PROC EEL.  For more information, please refer to the following: PROC EEL Statement documentation Expression Language: Reference Guide Using PROC EEL video by Arnold Toporowski   Acknowledgements Special thanks to my colleagues: Mickey Schauf for providing the inspiration to create this blog series. Andrew Shakinovsky for taking the time to let me talk through my ideas for this blog series enabling me to confirm that the concept was possible! Find more articles from SAS Global Enablement and Learning here. ... View more

With the November 2022 stable release (2022.11) there is now the capability to Implement SCD (Slowly Changing Dimensions) in a SAS Studio Flow.  This step requires a SAS Studio Engineer license.     SAS Studio supports two types of slowly changing dimension (SCD) scenarios:   Type 1: no history of data changes. Specified columns are overwritten in the target table without retaining a history of changes. This is useful for maintaining columns that are not used for historical analysis. Type 2: history of data changes is maintained. A new row is created in the target table each time a specified column is changed in the source table for a particular record. The most recent changed row is marked as current and the other rows for that record contain the history of its data changes.   The source table and target table for the SCD implementation must exist on the same database server.  Currently only the following databases are supported: Oracle, Singlestore, Snowflake, SQL Server, or Teradata.     Slowly Changing Dimension (SCD) Target Table Setup   The first thing you need to consider when implementing Slowly Changing Dimensions (SCD) is which column(s) from your source table you want to include in the target table and which type of SCD you want to track for each of those columns. On the target table, in addition to including the column(s) from the source table that you want to track you will need to include the primary key column(s) (unique identifier from source table) for SCD Type 1 and the following columns for SCD Type 2:   Primary key column(s) (unique identifier from source table) Effective Start Date Effective End Date Current Flag Surrogate Key (unique identifier for history records).   For my example, this is the design of my source table, Customer, for which I want to implement SCD.     Note that the primary key column is Customer_ID. I also have an Inactive column indicator.  It is best to mark a record in the source table as inactive rather than delete records when working with SCD.     I want to implement SCD Type 1 for the columns Title, FirstName, MiddleName, Inactive, and ModifiedDate and SCD Type 2 for the columns LastName, EmailAddress, and Phone. I also want to include the Notes column on the target table as an extra column.  Therefore, this the design of my target table, Customer_History.     Note that I have included the necessary additional columns for SCD Types 1 and 2:   Primary key column(s) (unique identifier from source table): Customer_ID Effective Start Date: Effective_Start Effective End Date: Effective_End Current Flag: Current_Flag Surrogate Key (unique identifier for history records):   Also, two of my column names in the target are slightly different than in the source table – PhoneNumber maps to Phone and InactiveCustomer maps to Inactive.       Create SAS Studio Flow for Slowly Changing Dimensions (SCD)   Now that I have all the needed information about my source and target tables and which types of SCD I want to perform on which columns, I am ready to build my SAS Studio Flow to implement SCD.     First, I need to add my source table to the SAS Studio Flow.  In my case, it is a SQL Server table named Customer.  Reminder:  SCD is currently only supported on the following databases: Oracle, Singlestore, Snowflake, SQL Server, or Teradata.     Next, I add the Implement SCD step from the Integrate section to the flow and connect it to my source table.  Reminder: this step requires a SAS Studio Engineer license.     I select my Target Table information for SCD.  Reminder the target table must exist on the same database server as the source table.     Next, on the Options tab, I specify the SCD options I want to implement.  When selecting columns for these options, a column from the target table can only be selected once.  In other words, a target table column cannot be selected as both a business key and a surrogate key.     The first required option is identifying the business key column(s) between the source and target.  In my case this is the CustomerID column on both tables.     Note:  When I select the column from the target table the corresponding source column is automatically mapped since they have the same name on both tables.     Next, I must specify column(s) for either SCD Type 1 or SCD Type2 or both.  In my case, I have decided to do both.     First, I select the target columns Title, FirstName, MiddleName, InactiveCustomer, and ModifiedDate for SCD Type 1.  I need to manually map InactiveCustomer to Inactive on the source table since the column names are different.     Next, I select the columns LastName, EmailAddress, and PhoneNumber for SCD Type 2.  I need to manually map PhoneNumber to Phone on the source table since the column names are different.     Since I selected to perform SCD Type 2, I must also select fields for tracking the changes and a surrogate key.  For tracking the changes, I select my Effective_From, Effective_To and Current_Flag columns on my target table.     For the required Surrogate key for SCD Type 2, I select the Customer_HistoryID column on my target table.     The final Options selection is for Include other columns.  This an optional selection and something to note is that any column(s) selected here are only written to the target table if a new row is added.  In other words, the value(s) from these column(s) are only written to the target table on the initial run and when SCD Type 2 is triggered by an update to the source table.  In my case, select the CustomerNotes column on the target table and map it to the Notes column on the source table.     I can view the Column Structure for the target table.      I review the Column Resolution tab. Columns with same names are matched together.     I select the Preview Data tab.  There aren’t any records to preview yet since my target table is empty.     I save my SAS Studio Flow to Implement SCD for my Customer table and add a description to the node.      Initial Run of SCD SAS Studio Flow   Now I am ready for initial run of the Slowly Changing Dimensions (SCD) SAS Studio Flow for my Customer table.  Below is the current state of the records for my Customer table.     I run my Implement SCD for Customer SAS Studio Flow.     I can view the submitted code for my SCD implementation.     My Customer_History table is a copy of the current state of my Customer table with the Effective_From, Effective_To, and Current_Flag columns completed.  This is the case since it is the initial run of SCD.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   SCD SAS Studio Flow Run After Source Table Update   The Customer table has been updated as follows since the last run of the flow.     The record for CustomerID 1 triggers SCD Type 1 for the update of the FirstName and MiddleName columns and SCD Type 2 for the update the Phone column.  The record for CustomerID 4 triggers only SCD Type 1 for the update of the Inactive column.     I re-run the Implement SCD for Customer SAS Studio Flow.     Now my Customer_History table is updated as shown below.     A new row is created for CustomerID 1 since it triggered an SCD Type 2 change.  Its SCD Type 1 changes are included in that row also and the previous row Current_Flag is changed to 0.  CustomerID 4 is simply updated with its new InactiveCustomer status since only SCD Type 1 was triggered for that row which also means its Notes from the source table were not updated in the Customer_History table since a new row was not created.       Considerations for Reporting on the Tables   For both tables in most cases I will want to filter for where Inactive=0 to make sure I am reporting only on current customers.  (Note this column on my target table is named InactiveCustomer).  Also, for the Customer_History table I will want to group the records by the Customer_ID column to ensure I’m reporting on the full history of a customer and include the Current_Flag column, so I know which record is current in the grouping. To ensure I am working with only the current records, I want to filter for Current_Flag=1.       Summary   The Implement SCD step is now available in SAS Studio Flow with the SAS Studio Engineer license for the following databases: Oracle, Singlestore, Snowflake, SQL Server, or Teradata.  For more information review the documentation for the step: SAS Help Center: Implement SCD: Storing and Managing Data over Time.   Acknowledgements   Special thanks to my colleagues @NicolasRobert and @UttamKumar for their assistance.     Find more articles from SAS Global Enablement and Learning here. ... View more