topic Re: Date as numeric in where clause in SAS Programming

Date as numeric in where clause

GN0001 — Thu, 14 Apr 2022 07:52:12 GMT

Hello team,
I have a variable as date numeric as mm/did/yyyy. I want to use it in a where clause: where dob= 12/10/2021;
SAS ignores it and returns other dates in the result, how can I fix this issue?
Blueblue

Re: Date as numeric in where clause

BrahmanandaRao — Thu, 14 Apr 2022 08:15:16 GMT

Use IF Clause instead where

Re: Date as numeric in where clause

japelin — Thu, 14 Apr 2022 08:17:38 GMT

try use this

where dob=input('12/10/2021',mmddyy10.);

Re: Date as numeric in where clause

GN0001 — Thu, 14 Apr 2022 08:28:33 GMT

But I don’t want to convert to literal. Thanks blue

Re: Date as numeric in where clause

japelin — Thu, 14 Apr 2022 08:35:20 GMT

then try

where dob=22624;

where dob='10dec2021'd;

12/10/2021 is not not a date for SAS.

So it must be converted to a SAS date value.

Re: Date as numeric in where clause

andreas_lds — Thu, 14 Apr 2022 08:58:18 GMT

@GN0001 wrote:
Hello team,
I have a variable as date numeric as mm/did/yyyy. I want to use it in a where clause: where dob= 12/10/2021;
SAS ignores it and returns other dates in the result, how can I fix this issue?
Blueblue

12/10/2021 is not numeric, but a string EDIT: not a string, of course, if there are not quotes, but an expression, as Kurt already pointed out

so what do you have in dob? If it is not a sas-date, fix this issue first.

Re: Date as numeric in where clause

Astounding — Thu, 14 Apr 2022 08:43:19 GMT

In SAS, 12/10/2021 means 12 divided by 10, divided by 2021. You don't have to convert your data but you do have to express the value you are comparing to as a date literal:

where date = '10dec2021'n;

SAS doesn't care whether you want to do this or not.

If you are pulling the data from a database instead of a SAS dataset, the database may impose an entirely different syntax.

Re: Date as numeric in where clause

Kurt_Bremser — Thu, 14 Apr 2022 08:52:27 GMT

12/10/2021

is a mathematical formula and results in 12 divided by 10 divided by 2021, so a very small number, which creates a SAS date of 01jan1960.

Either use a SAS date literal:

"12oct2021"d

or the INPUT function, as shown by @japelin . Even with quotes around it, your string is not a SAS date value.

Please (and I am telling you this for the umpteenth time now) STUDY THE DOCUMENTATION!

Re: Date as numeric in where clause

Kurt_Bremser — Thu, 14 Apr 2022 08:55:55 GMT

@GN0001 wrote:
But I don’t want to convert to literal. Thanks blue

What @japelin posted is in NO WAY a "conversion to literal", it is a conversion of your string to a SAS date value.

Re: Date as numeric in where clause

ballardw — Thu, 14 Apr 2022 10:48:18 GMT

If your SAS data set contains values that are dates (numeric and have some date format such as date9. or yymmdd10. assigned for readability) and you want to provide a literal value for comparison the value must be in the form 'ddMONyy'd

the year can be 2 or 4 digits.

So a where clause to test if an existing variable is

where dob= '10DEC2021'd;

the quotes and D following (no space) are what tells SAS you intend to used this as a date literal value.

Why that form? Likely because there are so many different strings of numbers that can represent dates (just look at the supplied SAS date formats for the common ones) that trying to program something that recognizes random digits as a date without some indication is night mare.

If that doesn't work then you need to provide a data step that recreates your values. Likely you would be needing to create a proper date value.

And before you ask, SAS uses the numeric value for comparison. The only time SAS would use the FORMATTED value in a comparison is if you force it by using something like:

If put(datevar,yymmdd10.) = '2021/01/01';

Not recommended.

Re: Date as numeric in where clause

GN0001 — Thu, 14 Apr 2022 16:12:47 GMT

Hello,
Does this filter my numeric date!?
Respectfully
Blueblue

Re: Date as numeric in where clause

GN0001 — Thu, 14 Apr 2022 16:25:10 GMT

My date is numeric, it is not a string. None of the approaches didn’t work.
Respectfully,
Blue

Re: Date as numeric in where clause

Kurt_Bremser — Thu, 14 Apr 2022 16:37:13 GMT

Post usable example data, so we know what we have to code against.

(usable = data step with datalines).

Re: Date as numeric in where clause

Tom — Thu, 14 Apr 2022 22:11:27 GMT

Check the TYPE , LENGTH and any FORMAT that is attached to your variable.

If the TYPE is NUM then check the range of the values using PROC MEANS or PROC SQL.

If the variable has DATE values then the range of values would probably fall in the interval from today's date, which is day number 22,749 down to the earliest date in your data. For example if you date goes back to beginning of 1960 then the smallest value is zero. If it goes back further you might have negative numbers.

If the variable has instead DATETIME values (number of seconds) then value falls in the interval from now, which second number 1,965,578,801 down to the earliest timepoint in your data. For example if you datetime values go back the start of 1960 then the smallest value is zero. If it goes back further you might have negative numbers.

If your code does not work then please post the SAS log from the step you ran that did not work. Include any error messages and notes. Make sure to use the Insert Code icon in the forum editor to paste the lines of text from the SAS log so the formatting is preserved. If there are no error messages in the LOG then explain why you know it did not work.