topic Re: compare macro variable dates not working in sas in SAS Programming

compare macro variable dates not working in sas

user112a2 — Fri, 26 Apr 2019 15:42:45 GMT

I need help on comparing two macro variable dates. Below is the code. You can see by the log that MINDT_AS_DATE_MINUS_ONE_DT1 is 23JUL2012 and TESTDT1 is 01JAN2013. However, when comparing SAS says 23JUL2012 <01JAN2013. is FALSE. I am very confused. Thanks

Removed

Not working in LOG, See:

Removed

Re: compare macro variable dates not working in sas

Reeza — Thu, 21 Feb 2019 21:19:17 GMT

Because you're doing a text comparison. SAS doesn't recognize those values as dates.

You can either not use a format and compare the raw numbers within %EVAL or %SYSEVALF or you can use quotation marks and a D to make them date literals in the comparison.

Date literals:

%macro intermediate;
%if %sysevalf("&MINDT_AS_DATE_MINUS_ONE_DT1."d < "&TESTDT1."d) %then %do; 
	%PUT this is true;
%end;
%else %do;
	%PUT this is false;
%end;
%mend intermediate;

%intermediate;

No formats option:

%let TESTDT=2013-01-01;
%let TESTDT1=%sysfunc(inputn(&TESTDT, yymmdd10.));
%LET MINDT = 2012-07-23;
%LET MINDT_AS_DATE = %SYSFUNC(INPUTN(&MINDT., YYMMDD10.));
%LET MINDT_AS_DATE_MINUS_ONE = %SYSFUNC(INTNX(MONTHS, &MINDT_AS_DATE., -1));
%LET MINDT_AS_DATE_MINUS_ONE_DT = %SYSFUNC(INPUTN(&MINDT., YYMMDD10.));


data _null_;
MINDT_AS_DATE_MINUS_ONE_DT=&MINDT_AS_DATE_MINUS_ONE_DT.;
call symput('MINDT_AS_DATE_MINUS_ONE_DT1',MINDT_AS_DATE_MINUS_ONE_DT);
run;

@user112a2 wrote:

%let TESTDT=2013-01-01;
%let TESTDT1=%sysfunc(inputn(&TESTDT, yymmdd10.), date9.);
%LET MINDT = 2012-07-23;
%LET MINDT_AS_DATE = %SYSFUNC(INPUTN(&MINDT., YYMMDD10.));
%LET MINDT_AS_DATE_MINUS_ONE = %SYSFUNC(INTNX(MONTHS, &MINDT_AS_DATE., -1));
%LET MINDT_AS_DATE_MINUS_ONE_DT = %SYSFUNC(INPUTN(&MINDT., YYMMDD10.));
data _null_;
MINDT_AS_DATE_MINUS_ONE_DT=&MINDT_AS_DATE_MINUS_ONE_DT.;
call symput('MINDT_AS_DATE_MINUS_ONE_DT1',put(MINDT_AS_DATE_MINUS_ONE_DT,date9.));
run;
%macro intermediate;
%if &MINDT_AS_DATE_MINUS_ONE_DT1.<&TESTDT1. %then
%do; 
%PUT this is true;
%end;
%else 
%do;
%PUT this is false;
%end;
%mend intermediate;
%intermediate;

Not working in LOG, See:

SYMBOLGEN: Macro variable MINDT_AS_DATE_MINUS_ONE_DT1 resolves to 23JUL2012
SYMBOLGEN: Macro variable TESTDT1 resolves to 01JAN2013
MLOGIC(INTERMEDIATE): %IF condition &MINDT_AS_DATE_MINUS_ONE_DT1.<&TESTDT1. is FALSE
MLOGIC(INTERMEDIATE): %PUT this is false
this is false

Re: compare macro variable dates not working in sas

Astounding — Thu, 21 Feb 2019 21:16:09 GMT

Macro variables hold text, not numeric values. So your comparison looks at the "2" at the beginning of "23JUL2012" and compares it to the "0" at the beginning of "01JAN2013". Since "2" is greater than "0", the first date is larger than the second date.

To get macro language to make a numeric comparison, you could use:

%if %sysevalf("&MINDT_AS_DATE_MINUS_ONE_DT1"d) < %sysevalf("&TESTDT1"d) %then %do;

That translates the date expressions into numeric values on SAS's date scale.

@Reeza looks like you are 1 minute quicker than I am!

Re: compare macro variable dates not working in sas

andreas_lds — Thu, 21 Feb 2019 21:18:21 GMT

You don't compare dates, but the strings "23JUL2012" and "01JAN2013" and 2 has a higher value in the ASCII table than 0, so 23JUL2012 < 01JAN2013 is false.

If you explain what you want to achieve, i am sure that the community will find a solution that works well without macro-code.

EDIT: Well, i am to late again 😉

Re: compare macro variable dates not working in sas

PaigeMiller — Thu, 21 Feb 2019 21:23:01 GMT

You can't use formatted dates in this command

%if &MINDT_AS_DATE_MINUS_ONE_DT1.<&TESTDT1.

You would be much better off not formatting the data for use in macro evaluations, and in fact formatting the dates so they are readable by humans is wrong here. You could also create and do the test in your data _null_, resulting in much simpler code, no formatting needed, and in fact formatting is wrong here.

data _null_;
    testdt = '01JAN13'd;
    mindt = '23JUL12'd;
    ... additional functions as needed ...
run;

If you must do the work in a macro variable, it would look something like this (I have chosen to code up a simpler problem than the one you are working on as an example)

%let testdt = %sysfunc(inputn(2013-01-01,yymmdd10.)); /* Note: no formatting here to date9.) */
%let mindt = %sysfunc(inputn(2012-07-23,yymmdd10.)); /* Note: no formatting here to date9. */

/* Do comparison */
%if &mindt < &testdt %then %put this is true;
%else %put this is false;

Re: compare macro variable dates not working in sas

Tom — Thu, 21 Feb 2019 21:18:49 GMT

Looks like it is working to me. The digit 2 is larger than the digit 0 in the lexical ordering of strings.

If you want SAS to treat those as dates you could convert them to date literals, but then you would need to use %SYSEVALF() if you wanted the macro processor to understand them.

1741  %let MINDT_AS_DATE_MINUS_ONE_DT1=23JUL2012;
1742  %let TESTDT1=01JAN2013;
1743  %IF %sysevalf("&MINDT_AS_DATE_MINUS_ONE_DT1"d < "&TESTDT1"d) %then %do;
1744    %put TRUE;
TRUE
1745  %end;
1746  %else %do;
1747    %put FALSE;
1748  %end;

Re: compare macro variable dates not working in sas

user112a2 — Thu, 21 Feb 2019 21:21:52 GMT

Thanks!! Worked like a charm.