SAS Viya: Filter Records by Date Custom Step Featuring the Date and Time Picker Control

[#1]

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.