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

With the November 2022 stable release (2022.11) there is now the capability to Remove Duplicates in a SAS Studio Flow.  This step is used to remove duplicate rows from an input table and create an output table with the unique rows.  The duplicate row could be based on all columns or specified column(s).     I want to remove duplicate records from my customer data set.   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 use the DQ – Match Code step from the public custom step repository to generate match codes for the Name, Address, and Zip fields to facilitate fuzzy matching on those fields when removing the duplicate records.      Next, I add the Remove Duplicates step from the Transform section to the flow.      I uncheck the option to Remove duplicates across all columns and add the condition to remove duplicates where the Name_MC, Address_MC, and Zip_MC columns contain the same values.      The Output tab has options to Replace existing output table with same name.  If the output table is a CAS Table, then you have the option to promote and/or save the table.  Also, if the output table is in PATH, DNFS, ADLS, or S3 CAS library, then you can specify the output format.      On the Debug tab, you have the option to select to Debug SAS macros.  I check this option for my flow.      I save and run the flow and now my duplicate customer records have been removed.       I review the Log and confirm the number of duplicate rows removed from my customer list.        Summary The Remove Duplicates step is now available in SAS Studio Flow.    For more information review its documentation: SAS Help Center: Removing Duplicates.   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 use the Date and Time Picker control to select a date/time in a custom step.  This control allows you to create a date, month, datetime or time picker (selection) in your custom step.     In this post, I will demonstrate using the date and time control to select a specific date and then filter the input records that are greater (more recent) than the specified date.     UI Design   For the user interface design, I create a page for the selection of the following:   Input table Date field Date selection (using the Date and Time Picker control) Output 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.    For the date and time control, I use the Date option type.     Note: there are options for Date, Month, Date and Time, and Time.   The About tab provides information about what the custom step does.  It is a recommended best practice to add this to all your custom steps.       Program   Below is a screenshot of the Program code to filter the records by the specified date in the user interface. I have highlighted where the Date and Time Picker control selection is referenced in the code.     Note that the code handles if the if the date column specified in the input table is invalid, date, or datetime.   Here is the full Program code for the Filter by Date custom step:   /* Filter Data By Date using the Date Picker Control */ /* Determine if DATE column is a valid date date type and if it includes TIME */ %let DT1=%sysfunc(find(&dateCol_1_format.,date,i)) ; %let DT2=%sysfunc(find(&dateCol_1_format.,datetime,i)) ; %put DT1=&DT1 ; %put DT2=&DT2 ; data _null_ ; if &DT1=1 AND &DT2=0 then call symputx('dateType',"'DATE'") ; if &DT1=1 AND &DT2=1 then call symputx('dateType',"'DATETIME'") ; if &DT1=0 AND &DT2=0 then call symputx('dateType',"'INVALID'") ; run ; %put dateType=&dateType ; /* Perform different actions depending on dateType value */ %if &dateType='DATETIME' %then %do ; proc sql ; create table &outTable. as select *, &dateCol_1_name as inputDate format=YYMMDD10. from &inTable. where datepart(inputDate) >= input("&selectedDate.", YYMMDD10.); alter table &outTable drop column inputDate; quit; %end ; %if &dateType='DATE' %then %do ; proc sql ; create table &outTable. as select *, &dateCol_1_name as inputDate format=YYMMDD10. from &inTable. where inputDate >= input("&selectedDate.", YYMMDD10.); alter table &outTable drop column inputDate; quit; %end ; %if &dateType='INVALID' %then %do ; %put ERROR: ** &dateCol_1_name ** is an invalid type for this step. Select a column that is of type DATE or DATETIME. ; %end ; Test the Custom Step   The easiest method for testing a custom step is to open it in stand-alone mode.  For more information, on this refer to my previous post.   To open the Filter by Date custom step in stand-alone mode, I right-click the custom step and select Open in a tab menu option.     I enter the following information:      I select the Run button to execute the custom step.  Below is the output I received by running the Filter by Date custom step.     Note the records are all greater than or equal to January 1, 2003.     Next, I change the Date column to Amount and Run the custom step again.  As expected, I get an error message that the Amount field is not a valid date field.         Summary   The Date and Time Picker control is now available for use in custom steps.  For more information on the date and time control review its documentation: SAS Help Center: Date and Time Control     Find more articles from SAS Global Enablement and Learning here. ... View more

With the August 2022 stable release (2022.1.4) there is now the capability to use the color picker control to select a color in a custom step.  Typical examples would include using the color picker control to select the color for a graph or report; however, in this post, I will demonstrate using the color picker control to select the color for the text of an email.   UI Design   For the user interface design, I create a page with the essential email information.   Copy Recipients section collapsed   Copy Recipients section expanded   Notice that the Body section has a note that if you want this text in a different use the color selector on the Options tab.     On the Options tab, not only is there the color picker control to select the text color for the email message, but there is also the selection of the importance of the email and whether to request a read receipt for the message.     There is also an Email Setup tab, to specify the SMTP mail host and port information.         The About tab provides information about what the custom step does.  It is a recommended best practice to add this to all your custom steps.     Program   Below is a screenshot of the Program code to send the email message with the selected options from the user interface.  I have highlighted where the color picker value is referenced in the code.     Here is the full Program code for the Send SMTP Email custom step:   /* Set email options */ options emailsys=smtp emailhost=&smtpHost emailport=&smtpPort ; /* if emailBody_count is empty (doesnt exist) create it and create emailBody_1 */ %global emailBody_count ; %if &emailBody_count eq %then %do ; %let emailBody_count=1 ; %let emailBody_1=&emailBody ; %end ; /* Format input information if multiple email addresses entered */ data _null_ ; newEmailTo=cats(transtrn(strip(compbl(translate("&emailTo"," ",",")))," ",'" "')) ; call symput('emailTo',strip(newEmailTo)) ; newEmailCC=cats(transtrn(strip(compbl(translate("&emailCC"," ",",")))," ",'" "')) ; call symput('emailCC',strip(newEmailCC)) ; newEmailBCC=cats(transtrn(strip(compbl(translate("&emailBCC"," ",",")))," ",'" "')) ; call symput('emailBCC',strip(newEmailBCC)) ; run ; /* Format and send email */ filename outmail email from="&emailFrom" to=("&emailTo") cc=("&emailCC") bcc=("&emailBCC") subject="&emailSubject" importance="&importance" /* Low Normal High. Default is Normal */ /* If ReadReceipt option is checked */ %if &readReceipt %then %do ; readreceipt %end ; ct="text/html" ; /* Build the body of the email */ data _null_ ; file outmail ; put "<html><body>" ; put "<p style='color: #&textColor'>" ; do i = 1 to &emailBody_count ; if symget("emailBody_" || strip(put(i,12.))) eq '' then do ; text=cats("<br>") ; end ; else do ; text=cats(symget("emailBody_" || strip(put(i,12.))),"<br>") ; end ; put text ; end ; put "</p>" ; put "</body></html>" ; run; /* Clear macro */ %symdel emailBody_count ;   Test the Custom Step   The easiest method for testing a custom step is to open it in stand-alone mode.    For more information, on this refer to my previous post.   To open the Send SMTP Email custom step in stand-alone mode, I right-click the custom step and select Open in a tab menu option.   I enter the following information on the Email Message Information tab:     I enter the following information on the Options tab:     Note that I have selected a color for the email text using the color picker control.     I enter the following information on the Email Setup tab:       I select the Run button to execute the custom step.  Below is the email I received by running the Send SMTP Email custom step.       Note the message was sent with High importance and the text color is the color specified in the options of the custom step.     Summary   The color picker control is now available for use in custom steps.  For more information on the color picker control review its documentation: SAS Help Center: Color Picker Control.       The Send SMTP Email custom step I highlighted in this blog can be downloaded from here.   Acknowledgements Thanks to my colleague, @NicolasRobert , for his programming assistance with this custom step!   ... View more

With the July 2022 stable release (2022.1.3) there is now the capability to run a Custom Step in a stand-alone tab in the SAS Studio workspace.  In this blog, I will demonstrate working with a stand-alone custom step by using a custom step I created in a previous post.   Open a Custom Step in Stand-Alone Mode   To use a Custom Step outside of a SAS Studio Flow in stand-alone mode, you select to open the custom using the Open in a tab menu 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.    This opens the custom step as a stand-alone step.      Notice that in stand-alone mode the input table and output table controls are visible whereas when the custom step is used in a flow they are simply input and output ports.  Based on this difference, I will want to edit the custom design to give more meaningful labels to these controls.  I will do that in the next section, but first let see how a custom step works in stand-alone mode.   In the stand-alone custom step tab, I make selections for the controls.    I click    to select the input and output table.  I also make my selections for State, County, and Cities.  I then click Run to execute the custom step in stand-alone mode.        The step executes the code in the custom step with the specified selections and the output is written to location selected.  You can view log by selecting Code panel and then Log tab.      You can also download the log, by selecting     → Download, then you have the option download the log file as text or html.      I confirm that the output was created in the location I specified.     Edit Custom Step for Stand-Alone Mode   Now that I have tested that my custom works in stand-alone mode, I want to make some changes to better support the stand-alone mode.  To do this I need to open the custom step in design mode by selecting Edit in Designer.      The custom step is now open, and I can make changes to its design.      I change the label for the input table control and assign a default library and table.  Note the default library and table selection is only used for stand-alone mode; it does not affect using the custom step in a flow.      For the output table control I update its label.      I save and close the custom step designer.  I then select     on the stand-alone custom step tab to refresh it to the latest saved design and program for the custom step.      Note the labels for the input and output table have been updated as well as the default input table is already set.  You can click      to change the default selection.  I make my selections and select Run to execute the custom step in stand-alone mode.     Save the Custom Step as a SAS Program   To save the stand-alone custom step with its selections as a SAS Program, after the custom step is run, then select Edit code.      This opens a tab with the SAS code based on the current selections and does NOT affect the saved custom step.  You can now save and run this code as a SAS program.     Schedule a Stand-Alone Custom Step as a Job   To schedule a stand-alone custom step as job, first select values for all the required controls.  Then, select     → Schedule as a job.     Summary   Now custom steps can be used outside of SAS Studio Flows and be executed as a stand-alone task.  For more information review: SAS Help Center: Working with Stand-Alone Custom Steps   Find more articles from SAS Global Enablement and Learning here. ... View more

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

The Execute Decisions step enables you to use the logic of your SAS Intelligent Decisioning Decisions within a SAS Studio Flow. The use of the Execute Decisions step requires a SAS Studio Engineer license  and SAS Intelligent Decisioning.  It also requires the following:   The SAS Intelligent Decisioning Decision must be published to CAS The input to the Execute Decisions step must be a CAS table.   CAS Publication Destination   The SAS Intelligent Decisioning Decision must be published to CAS.  If a CAS Publishing Destination doesn't exist, then a SAS Administrator needs to create one in SAS Environment Manager.     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 more information on creating Publishing Destinations, refer to its documentation.       CAS Table Input   The input to the Execute Decisions step must be a CAS table, so ensure the table you want to use as the input to the decision is loaded to memory in CAS.     Decision Published to CAS   Build the decision you want to use in your SAS Studio Flow in SAS Intelligent Decisioning.     Test the decision in SAS Intelligent Decisioning with your CAS input table.     Publish the decision to a CAS publishing destination.     Test the published decision in Publishing Validation with your CAS input table.     For more information on creating, testing, and publishing decisions, refer to the SAS Intelligent Decisioning documentation.       Create SAS Studio Flow using Execute Decisions Step   In SAS Studio, create a new Flow and on the Snippets pane, right-click the snippet Generate SAS librefs for caslibs and select Add to flow.     This code creates a CAS session and creates SAS librefs for existing caslibs, so they are visible in the Libraries pane.  You need to run this step to select the CAS table you are using as input for your Execute Decisions step.     Next in the Libraries pane, right-click the CAS input table and select Add to flow to add it to the flow.     On the Steps pane, double-click the Execute Decisions step located in the Integrate section to add it to the flow.  Connect it to the CAS input table node.     Select the published decision.  Note:  Only decisions published to a CAS destination are available for selection.       Next on the Input variables tab, map the fields from the source table to the input variables of the selected decision.  Fields with the same name and type are auto-mapped for you.  Note: All columns must be successfully mapped for the Execute Decisions step to run.     The Output variables are the output variables from the selected decision.     Save and run the flow to test it.  Preview the output of the Execute Decisions step.     If I close the saved flow, I can re-open and run it again. I can also add any step(s) to the flow as desired to work with the output of the selected decision.  For example, in my case I could branch the flow based on whether or not the car should be considered for purchase.     For more information on working with SAS Studio Flows, refer to its documentation.    Summary   The Execute Decisions step is used to run a SAS Intelligent Decisioning decision that is published to CAS in SAS Studio Flow.  For more information on this step, please refer to its documentation.           Find more articles from SAS Global Enablement and Learning here. ... View more

The Load Table step enables you to load a source table into a target table. With the Load Table step, you can control how data is loaded into the target table. You can insert new source rows into the target table, update existing rows in the target table, or both. Also, if needed you can control how existing rows in the target table are removed before new rows are inserted.  The use of the Load Table step requires a SAS Studio Engineer license.     The example I am going to walk through in this blog is inserting records into an existing table using the Load Table step.     I have a file of new records to insert into the PRDSALE table in my SASDM library.  The file has 720 new records to insert in the existing PRDSALE table with 1440 records, so after inserting the new records the updated PRDSALE table should have 2160 records.     I add the file with the new records to be inserted and import it in a SAS Studio Flow.  Note:  Currently the input data to be inserted/updated must have column names exactly match the ones you want to affect in the target 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.   Now, I add the Load Table step from the Integrate section to the flow.     On the Target Table tab of the Load Table step, I select the Library and Table name where the new records are to be inserted.     On the Options tab, I select Insert rows for the load technique.  If I wanted to Update rows or Upsert (update and insert) rows, then I would also need to specify the row(s) that uniquely identify a record in the table (primary key).  I select No action for the preprocess action.  If needed I could choose to truncate table, delete all rows, or replace the table if it exists instead.  Depending upon the target table type, there may also be Output Table Options for selection, or I could manually specify some by selecting the Advanced table options link.     On the Column Structure tab, you can review the structure of the target table.     On the Column Resolution tab, the input columns are mapped to the target columns based on name. The column RECORD_TYPE column in the input file does not exist in the target table, so it is ignored and the PREDICT column does not exist in the input file.     On the Preview Data, you can preview the current state of the target table.       I run my flow and when I review the resulting PRDSALE table in the SASDM library, there are now the expected 2160 records.  The new records have been inserted into the table.     Summary   The Load Table step provides a way to update, insert, or upsert (update and insert) data into a table.  For more information on this step, please refer to its documentation.    Find more articles from SAS Global Enablement and Learning here. ... View more

In this blog, I show how you can read in a delimited text file containing reference data using a custom step.  Then, use that reference data in a dynamic drop-down within another custom step.  These custom steps use the File or Folder Selector control (available in 2021.2.1) and the ability to create a Dynamic Drop-down List using the drop-down list control (available in 2021.2.3).   Custom Step to Read-In a Reference File This custom step reads in a delimited reference file, so its values can be used in a SAS Studio Flow.  The reference file can either be in SAS Content or on the SAS Server.  The first row of the reference file must contain the column names.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Below is the JSON code for the UI of the custom step:   { "showPageContentOnly": true, "pages": [ { "id": "refFileImport", "type": "page", "label": "Import Delimited Reference File", "children": [ { "id": "text1", "type": "text", "text": "Read in a delimited Reference file from either SAS Content or SAS Server.", "visible": "" }, { "id": "referenceFile", "type": "path", "label": "Select the reference file", "pathtype": "file", "placeholder": "", "required": true, "visible": "" }, { "id": "note", "type": "text", "text": "NOTE: This must be a delimited text file and the first row of the file must contain the column names.", "visible": "" }, { "id": "delimiterType", "type": "dropdown", "label": "Select file delimiter", "items": [ { "value": "Comma" }, { "value": "Space" }, { "value": "Tab" }, { "value": "Other" } ], "required": false, "placeholder": "", "visible": "" }, { "id": "otherDelimiter", "type": "textfield", "label": "If Other, enter delimiter", "placeholder": "", "required": true, "visible": [ "$delimiterType", "=", "Other" ] }, { "id": "numRows", "type": "numberfield", "label": "Enter number of rows to guess column data types", "placeholder": "", "required": true, "max": 10000, "min": 10, "excludemin": false, "excludemax": false, "visible": "" }, { "id": "outTable", "type": "outputtable", "label": "Output Table", "required": true, "placeholder": "", "visible": "" } ] } ], "values": { "referenceFile": "", "delimiterType": { "value": "Comma" }, "otherDelimiter": "", "numRows": 100, "outTable": { "library": "", "table": "" } } }   Below is the code performed by the custom step:   /**** Read in a delimited text file. The intention of this custom step is this file would contain reference data that is needed in the SAS Studio Flow. ****/ /**** NOTE: The first row of the file must contain a header row ****/ /* Macro for default SAS Server temp folder */ %let srvrFolder=/tmp; /* Parse parts of the selected Reference File */ %let fileLoc=%qscan(&referenceFile,1,:); %put FileLocation: &fileLoc; %let filePathwithName="%qscan(&referenceFile,2,:)"; %put ReferenceFilePathWithName: &filePathwithName; %let refFileName="%qscan(&referenceFile,-1,/)"; %put ReferenceFileName: &refFileName; %let noquotePath=%qscan(&referenceFile,2,:); %put PathWithNoQuote: &noquotePath; %let parentFolderLength=%eval(%length(&noquotePath)-%length(&refFileName))+1; %let refFilePath="%qsubstr(&noquotePath,1,&parentFolderLength)"; %put ReferenceFilePath: &refFilePath; /*Perform this section if file on SAS Content */ %if &fileLoc=sascontent %then %do; filename refFile filesrvc folderpath=&refFilePath filename=&refFileName; filename srvrFile '&srvrFolder.\%dequote(&refFileName)'; data _null_; infile refFile; file srvrFile; input @; put _infile_; run; %end; /*Perform this section if file on SAS Server */ %if &fileLoc=sasserver %then %do; filename srvrFile &filePathwithName; %end; /* Import selected reference file */ %if &delimiterType=Comma %then %do; proc import datafile=srvrFile out=&outTable replace dbms=dlm; delimiter=',' ; getnames=YES; guessingrows=&numRows; run; %end; %if &delimiterType=Tab %then %do; proc import datafile=srvrFile out=&outTable replace dbms=tab; getnames=YES; guessingrows=&numRows; run; %end; %if &delimiterType=Other %then %do; proc import datafile=srvrFile out=&outTable replace dbms=dlm; delimiter="&otherDelimiter" ; getnames=YES; guessingrows=&numRows; run; %end; %if &delimiterType=Space %then %do; proc import datafile=srvrFile out=&outTable replace dbms=dlm; delimiter='20'x ; getnames=YES; guessingrows=&numRows; run; %end;   Note:  I am a novice at SAS coding, so there may other (better) ways to accomplish this.     Custom Step with Dynamic Drop-Down List   The input data provided to this custom step is used to dynamically create the list of values for the drop-down.  In my example, the input data is the discount reference data read in using the above discussed custom step.     Below is the JSON code for the UI of the custom step:   { "showPageContentOnly": true, "pages": [ { "id": "discount", "type": "page", "label": "Select Discount Percentage", "children": [ { "id": "text1", "type": "text", "text": "Example of calculating the discount multipler. Input must contain percentage values.", "visible": "" }, { "id": "inTable", "type": "inputtable", "label": "Input Discount Table", "required": true, "placeholder": "", "visible": "" }, { "id": "discountColumn", "type": "columnselector", "label": "Select the discount tier column", "order": false, "columntype": "c", "max": 1, "min": 1, "visible": "", "table": "inTable" }, { "id": "discountPercentage", "type": "dropdown", "label": "Discount Percentage", "items": { "ref": "discountColumn" }, "required": true, "placeholder": "", "visible": "" }, { "id": "outTable", "type": "outputtable", "label": "Output DiscountTable", "required": false, "placeholder": "", "visible": "" } ] } ], "values": { "inTable": { "library": "", "table": "" }, "discountColumn": [], "discountPercentage": null, "outTable": { "library": "", "table": "" } } }   Below is the code performed by the custom step:   /* Convert Discount Percentage to Numeric Amount */ %let discPercentage = %qsubstr(&discountPercentage,1,%length(&discountPercentage)-1); %put DiscountPercentage: &discPercentage; %let discCalculation = %sysevalf((100-&discPercentage)/100); %put DiscountCalculation: &discCalculation;   Note:  I am a novice at SAS coding, so there may other (better) ways to accomplish this.   SAS Studio Flow Example using the Custom Steps   The GIF below shows the two custom steps in action in a SAS Studio Flow and the selected discount percentage is then used to calculate the car price based on its MSRP value.       Summary For more information on custom steps, please refer to its documentation.   Thanks to my SAS colleagues Tina Tart for explaining current state of the custom step controls and Tan Phan for his code to parse the parts of the selected file. Also, thanks to  @StephenFoerster for his coding assistance and support.   Find more articles from SAS Global Enablement and Learning here. ... View more

The SAS Viya 2021.2 (November 2021) Long Term Support (LTS) release provides a means for SAS Data Integration (DI) Studio users to migrate their jobs to SAS Viya by creating SAS Studio Flows.   Currently, the code in SAS DI Studio jobs is converted to SAS Program nodes in SAS Studio Flows.  In future releases the code will be mapped to specific SAS Studio Flow nodes where appropriate such as Query, Filter Rows and Sort as is done now for SAS Enterprise Guide (EG) projects.   There is a content assessment to help support you SAS Viya content migration.  Refer to this article for more information.     Migrate SAS Data Integration (DI) Jobs This is the DI Studio job I want to migrate to SAS Viya.     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 first step is to create an export package based on this job.     When creating the export package, select the option to Include dependent objects when retrieving initial collection of objects.     Once the export package is created, you may need to copy or FTP it to a location accessible by your SAS Viya installation.  In SAS Viya, access the Import page in SAS Environment Manager.     Select   to navigate to the SAS package file.  Select to import only the DI Studio job(s).  The library and tables won't import and there will be errors.     On the Mapping tab, select   to navigate to the location to save the converted SAS Studio Flow file(s).     Note:  There is no need to map the tables since they are not being imported.     Select the Preview tab to confirm the contents to be imported, then select Import.     Confirm the import completed on the Results tab.     Now, the resulting flow file(s) can be opened in SAS Studio.     When you first open the flow, there is a message that "The flow contains migration errors".  If you hover over the error indicator on the Job pre-code node, the error is displayed.     In my case, the migration error is that the library difttgt is required for this job to run successfully.  All the code from the DI Studio job has been converted to SAS Program nodes and a Job pre-code and Job post-code nodes are created and put into swimlanes in the appropriate execution order.   In future releases, code will be converted to more specific nodes instead of just the SAS Program as has already started happening for EG Project migrations.     Also, be aware that the SAS Program nodes use hard-coded table names.  It is a copy of the code from the DI Studio job; therefore, it does not use the macro variables that represent the tables connected on the input/output port of the SAS Program node.  You may want to take advantage of this feature in SAS Studio Flows.  If so, for more information refer to the Using Macro Variables to Reference Input and Output Ports topic in the documentation.     To clear the error message, I can uncomment the LIBNAME statement in the Splitter node of my flow and correct it to fit my Viya installation.  In my case, I would need to adjust the path for the SAS Data Sets in the flow.     Another way to clear the error message is to create the SAS Library on the Libraries tab of SAS Studio.  This is the option I am going to do.  Since the library I need to create is for BASE SAS, I need to confirm on the Explorer tab, the location on the SAS Server where the data sets reside.  Note:  You may need to upload or migrate your data to be accessible to your Viya installation.     On the Libraries tab, I create the SAS library pointing to the location.     I can now close the error message and click the Job pre-code node to make the error indicator disappear.  I save and run the job.  The Job pre-code node completes successfully, but there is an error on the Splitter node.     I review the Log and find out the error is due to a missing format for ORDER_TYPE.     To clear this error, I can import my custom formats into my SAS Viya installation.  Refer to this article on how to do this.  Another option is to add the format into the code of my flow.  I am going to do that and add the proc format statement using a SAS Program node that I connect to the Job pre-code node.     I save and re-run the flow.  This time it completes successfully.     I navigate to the DIFTTGT SAS Library and confirm my output tables were created.     I have now confirmed that I successfully migrated my DI Studio job!       Summary Users of SAS DI Studio can now begin migrating to SAS Studio in SAS Viya.  The jobs are converted to SAS Studio Flows, but keep in mind you also need to migrate any needed source data, create libraries, and migrate custom formats as well as any other supporting items for the job.     For more information review the topic Transitioning to SAS Studio in SAS Viya in the documentation.    Find more articles from SAS Global Enablement and Learning here. ... View more

The SAS Viya 2021.1.4 stable release (August 2021) introduces the ability to control the execution order of individual flows within a flow file in SAS Studio.  This is done by grouping the individual flows (or nodes) into "swimlanes" and specifying the order in which the "swimlanes" are executed.     Below is an example of a SAS Studio Flow with "swimlanes" to control the execution order. In this blog, I will discuss how the flow was created.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   SAS Studio Flow Submission Order Before the introduction of swimlanes in SAS Studio Flow, if my flow file was divided in the same three chunks, I had to ensure by position that the three sub-flows were positioned just right in order to have them execute in the correct order.     If I just slightly move the Terminate CAS Session node, then the flow errors since the CAS session is terminated before it is used in the Promote Table to CAS node.     However, now in the SAS Viya 2021.4 stable release (August 2021) I can explicitly control the order of these three sub-flows to ensure they will always run in the proper order.  I can also name the sub-flows to help document the overall flow.     To organize my flow into swimlanes, I select:  .     This opens the Submission Order pop-out window.  Select the slider to Enable submission order and create swimlanes for each of the individual sub-flows.     I can enter names each of the created swimlanes.     Also, on the Submission Order pop-out window I can refresh the arrangement for the selected swimlane(s), add/delete swimlanes, and rearrange the order of the execution for the swimlanes.   Note: If you disable the submission order feature, any changes you have made to the swimlane names and order are not saved.     My flow is now organized and is guaranteed to run in the proper order.      Summary For more information on this newly available feature to control the submission order in SAS Studio Flows in the 2021.1.4 (August 2021) stable release review its documentation.         Find more articles from SAS Global Enablement and Learning here. ... View more