I am trying to use date constants in a numeric range.
Example:
data t;
input date;
format date ddmmyy10.;
datalines;
0
1
2
3
4
5
6
7
;
run;
proc print data=work.t;
*where date in (3:6);
where date in ('04JAN1960'd : '07JAN1960'd);
run;
The first where clause uses the numeric date and the programs execute without errors.
The second where clause uses the date constant and will generate an error.
The documentation specifies that the N and M should be numeric.
My question is why would it fail with a date constant or even a variable that is numeric when using the range feature with the IN operator.
M, N, and all the integers
I would assume that the rules for parsing the command doesn't support handling conversion of date literals. It would also probably fail if you tried to use hexadecimal or binary literals also.
You can use macro functions to convert your human readable date into an integer that the SAS compiler will understand. Try wrapping the date literals inside of the %SYSEVALF() function.
20 data x;
21 set sashelp.class ;
22 where age in (10:%sysevalf('01JAN2017'd));
23 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
WHERE (age=INT(age)) and (age>=10 and age<=20820);
NOTE: The data set WORK.X has 19 observations and 5 variables.
I would assume that the rules for parsing the command doesn't support handling conversion of date literals. It would also probably fail if you tried to use hexadecimal or binary literals also.
You can use macro functions to convert your human readable date into an integer that the SAS compiler will understand. Try wrapping the date literals inside of the %SYSEVALF() function.
20 data x;
21 set sashelp.class ;
22 where age in (10:%sysevalf('01JAN2017'd));
23 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
WHERE (age=INT(age)) and (age>=10 and age<=20820);
NOTE: The data set WORK.X has 19 observations and 5 variables.
Thank you, Tom.
The solution works. I just wonder why it would fail when using a date
constant or numeric variable.
Explanation on SAS inner workings with %sysevalf:
Using the macro function the macro processor will first run and execute the macro code.
The macro processor will convert the following code:
if curr_date in (%sysevalf('01FEB2017'd) : %sysevalf('28FEB2017'd)) then do;
to
if curr_date in (20851 : 20878) then do;
and then the compiler can run the code as if it was numeric.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.