topic Re: Restricting to a range of dates in MM/DD/YYY format in SAS Programming

Restricting to a range of dates in MM/DD/YYY format

Wolverine — Mon, 20 Feb 2023 19:16:24 GMT

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".

Re: Restricting to a range of dates in MM/DD/YYY format

Tom — Mon, 20 Feb 2023 19:36:56 GMT

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

Re: Restricting to a range of dates in MM/DD/YYY format

mklangley — Mon, 20 Feb 2023 19:40:27 GMT

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;

Re: Restricting to a range of dates in MM/DD/YYY format

Wolverine — Mon, 20 Feb 2023 19:45:12 GMT

@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.

Re: Restricting to a range of dates in MM/DD/YYY format

Tom — Mon, 20 Feb 2023 19:56:10 GMT

@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.