BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
arnouxvr
Fluorite | Level 6

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.

 

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p00iah2thp63bmn1lt20...

 

M, N, and all the integers

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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.
arnouxvr
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1885 views
  • 1 like
  • 2 in conversation