BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Wolverine
Quartz | Level 8

This should be simple, but I can't get it to work 😡  Here are the variable formats from proc contents:

birth_infantdob Num 8 MMDDYY.
last_reg_dob Num 8 MMDDYY10.

 

I've tried several variations of this approach, but I keep getting errors

 

data want;
	set have;
	where ('01/01/2022'<=birth_infantdob<='09/30/2022'
		or '01/01/2022'<=reg_infantdob<='09/30/2022');
run;

 

This give me ERROR: WHERE clause operator requires compatible variables.

I also tried removing the single quotes from around the dates, but then that says it's an "obvious false WHERE clause".

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mklangley
Lapis Lazuli | Level 10

Your WHERE clause is comparing numeric date variables (birth_infantdob and last_reg_dob) with strings ('01/01/2022' and '09/30/2022'). The dates you have hard-coded are, currently, just character strings. To make them SAS date values, use the DATE9. format (e.g., "01JAN2022"d).

 

So try this:

data want;
	set have;
	where ("01Jan2022"d <= birth_infantdob <= "30SEP2022"d
		or "01Jan2022"d <= reg_infantdob <= "30SEP2022"d);
run;

 

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

So from your PROC CONTENTS results you can see that the variables are NUMERIC.  So you cannot compare the values to strings like '01/01/2022'.  You need to compare them to DATE values.  You could type in the actual number that SAS uses to represent the first day of 2022, which is 22646.  But it will probably be a lot easier to type and easier to understand if you use a DATE literal instead.  DATE literals must use a string that the DATE informat can understand which is enclosed in quotes and has the letter D appended:

 

Example:

1    data _null_;
2      x='01JAN2022'd ;
3      put x= ;
4    run;

x=22646

Remember that the format attached to a variable just determines how the value is DISPLAYED, not what it stored.

5    data _null_;
6      x='02JAN2022'd ;
7      put x= / x= yymmdd10. / x= mmddyy10. / x= ddmmyy10. ;
8    run;

x=22647
x=2022-01-02
x=01/02/2022
x=02/01/2022
Wolverine
Quartz | Level 8

@Tom wrote:Remember that the format attached to a variable just determines how the value is DISPLAYED, not what it stored.

Excellent point -- that is the source of my confusion!  The variables are displayed as 02/20/2022, but that is not how SAS is actually storing them.

Tom
Super User Tom
Super User

@Wolverine wrote:

@Tom wrote:Remember that the format attached to a variable just determines how the value is DISPLAYED, not what it stored.

Excellent point -- that is the source of my confusion!  The variables are displayed as 02/20/2022, but that is not how SAS is actually storing them.


Right.  SAS uses the term as defined by meaning 6a in the dictionary.com definition.  Although SAS does use the term informat for the rules used for INPUT and reserves the term format for OUTPUT.

mklangley
Lapis Lazuli | Level 10

Your WHERE clause is comparing numeric date variables (birth_infantdob and last_reg_dob) with strings ('01/01/2022' and '09/30/2022'). The dates you have hard-coded are, currently, just character strings. To make them SAS date values, use the DATE9. format (e.g., "01JAN2022"d).

 

So try this:

data want;
	set have;
	where ("01Jan2022"d <= birth_infantdob <= "30SEP2022"d
		or "01Jan2022"d <= reg_infantdob <= "30SEP2022"d);
run;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 771 views
  • 0 likes
  • 3 in conversation