SAS Macro Programming: A couple of SAS Macros to validate dates in macro variables.

[PeterKellberg]

Hi,

I have made a couple of macros that makes life much easier when you have to deal with dates in macro variables. The examples illustrate the use from within a macro. Please feel free to put them in your SAS Macro Library for easy use.

Have a nice weekend 😉

isValidDate
Returns True (1) if the date is valid and False (0) if not.

%macro isValidDate(date);
    %local rc datevalidation;
    %let rc=%sysfunc(dosubl(%str(data _null_;date=input("&date", ?? ANYDTDTE60.);call symputx("datevalidation",date);run;)));
    %if &datevalidation=. %then %do;
        0
        %return;
    %end;
    %else %do;
        1
        %return;
    %end;
%mend;

Example

%let date=2018-10-33;

%if not %isValidDate(&date) %then %do;
%put ERROR: DATE is not a valid date!;
%return;
%end;

toDate
Converts a text date to a SAS date. Can be used when comparing two dates.

%macro toDate(date);
    %local rc datevalidation;
    %let rc=%sysfunc(dosubl(%str(data _null_;date=input("&date", ?? ANYDTDTE60.);call symputx("datevalidation",date);run;)));
    &datevalidation
%mend;

Example

%let date_depart=2018-12-05;
%let date_arrival=2018-10-07;

%if %toDate(&date_depart) > %toDate(&date_arrival) %then %do;
    %put ERROR: Departure date after arrival date!;
    %return;
%end;

toDay

Gives you todays date.

%macro toDay;
    %sysfunc(today())
%mend;

Example

%let registrationdate=2018-10-07;

%if %toDate(&registrationdate) > %toDay %then %do;
    %put ERROR: Registration date after today!;
    %return;
%end;

[mkeintz]

DOSUBL is quite a hammer, but I do not see this task as a nail.   The datevaliddation macro can be done without invoking a data step, which would surely be more expensive than embedding an INPUTN function and IFN function in nested %sysfunc's.   It would be difficult for  me to recommend  DOSUBL-dependent approaches when other techniques are readily available.

Consider:

%macro valdate(date);
  %local date;
  %sysfunc(ifn(%sysfunc(inputn(&date,ANYDTDTE60.))=.,0,1))
%mend;

%let rslt=%valdate(41jan2011);
%put &=rslt;

The same approach could be used for the TODATE macro.

[PeterKellberg]

Thanks for your thoughts 😉 I know that we dont want a datastep when not needed and especially not in "Boolean" macros. That is  why I used DOSUBL. And why datastep? Because the ?? (to suppress warnings if invalid date - I dont want them) in the INPUT function simply is not available in the INPUTN function. I cant wait until monday to try your solution.

Regards, Peter

[mkeintz]

@PeterKellberg

I revised your macro by removing the ??, and used it against an invalid date (2018-13-33), and get no warning message, (and same in my alternative), even though it does produce a warning when run in normal code.  Are you sure you need the "??" inside the macro?  

[Quentin]

Good point @mkeintz.  I was surprised to see that ANYDTDTE informat doesn't actually generate invalid data errors:

68   data _null_ ;
69     date=input("foo", ANYDTDTE60.) ;
70     put date= ;
71   run ;

date=.

Found this usage note that describes it:  http://support.sas.com/kb/14/291.html

Does that fact that it's a usage note rather than a problem note mean that this is an intended feature?  Or since the table does show a 'fixed' column with null values, does that suggest it could be fixed in a future version, so that invalid values would throw an error?

I typically stay away from anydtdte, as it seems like more guessing than I would want.  But I suppose if one is willing to take some mess of differently formatted date strings and push it through anydtdte, accepting its interpretation of the strings, they may not want errors for any values where anydtdte guesses that it's not a date.

[Tom]

You cannot use INPUT()  with %SYSFUNC(), but you can use INPUTN() and INPUTC() instead.

I always assumed it just made it easier to implement for %SYSFUNC() to know whether to treat the result as string or a number.

[PeterKellberg]

Thanks for the supreme input from you!

What did I learn? The ANYDTDTE informat does not produce messages in the log like this (as do any other DATE informat - I presume). 

NOTE: Invalid argument to function INPUT at line 7 column 10.
date=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 7:10

So if we choose the ANYDTDTE informat for checking a date for being valid we can use the INPUTN function straight away:

%macro isValidDate(date);
    %if %sysfunc(inputn(&date,ANYDTDTE10.)) = . %then
        0;
    %else
        1;
%mend;

or more compact

%macro isValidDate(date);
    %sysfunc(ifn(%sysfunc(inputn(&date,anydtdte10.)),1,0))
%mend;

As mentioned the ANYDTDTE informat may not be the favorite - we want perhaps to use the yymmdd informat:

/* input registration date on the form yyyy-mm-dd */
%let regdate=2018-10-08;

Because the yymmdd informat will produce annoying messages in the log when it encounters an invalid date AND ?? to suppress those messages is not available in the INPUTN function - the solution is a DATA step that can make use of the INPUT function.

We cannot just have that DATA step inside the macro because the code is passed back to the Input Stack. The whole point in this is to make a macro that only returns TRUE/FALSE. Ordinary code will ruin this approach. Somehow the DOSUBL function does not do that.

So thats why I chose that solution. A variant in order to supply your choice of DATE informat:

%macro isValidDate(date,informat);
    %local rc datevalidation;
    %let rc=%sysfunc(dosubl(%str(data _null_;date=input("&date", ?? &informat);call symputx("datevalidation",date);run;)));
    %if &datevalidation=. %then
        0;
    %else
        1;
%mend;

And the testing will look like this

%if not %isValidDate(&date,yymmdd10.) %then %do;
    %put ERROR: The date is invalid. Enter a valid date in the format yyyy-mm-dd.;
    %return;
%end;

Regards,

Peter

[Quentin]

Nice.  So as a general point, %SYSFUNC allows you to call almost all SAS functions from the macro language.  For functions that cannot be called from the macro language, you can wrap them in a function-style macro with a DOSUBL block called by %SYSFUNC.  For those wanting to read more about such macro functions, see Rick Langston's excellent paper: https://support.sas.com/resources/papers/proceedings13/032-2013.pdf. 

[PeterKellberg]

Thx Quentin 😉 Hat off for Mr. Rick. Again I learned something new: PROC STREAM.