topic Re: How to pass literal date to where clause in SAS Programming

How to pass literal date to where clause

current_thing — Thu, 16 May 2024 17:00:15 GMT

my query:

proc sql;
select * from temp where 01/01/24 between col1 and col2;
quit;

feel silly to ask but how can I pass the date that already is in date format? thanks!

Re: How to pass literal date to where clause

PaigeMiller — Thu, 16 May 2024 17:09:36 GMT

Date literals must be and can only be in this form: '01JAN2024'd (except you can use lower case letters)

proc sql;
select * from temp where '01JAN2024'd between col1 and col2;
quit;

But you said " how can I pass the date that already is in date format", are you asking about a variable in a data set that is in date format? You didn't really say "variable" anywhere, but if that's what you mean, and let's assume the variable name is DATE and it contains true SAS date value (does it?) then you can use

proc sql;
select * from temp where DATE between col1 and col2;
quit;

but there are a lot of assumptions needed to make that work. If it is not working, then explain in a lot more detail, and provide not only the PROC CONTENTS of your data set, but also typical values of DATE and COL1 and COL2.

Re: How to pass literal date to where clause

current_thing — Thu, 16 May 2024 17:13:40 GMT

I know I can reformat as '01Jan24'd and that works, but I want to literally pass 01/01/24 without having to reformat. Does that make sense?

Re: How to pass literal date to where clause

PaigeMiller — Thu, 16 May 2024 17:22:10 GMT

To answer directly your question about can it be 01/01/24, the answer is NO

We need a much more detailed explanation. Include in your explanation:

Give background of this problem. Background means explanation, it should not address any SAS code issues.
Why does it have to be 01/01/24 looking just like that? What is wrong with some other appearance such as '01JAN2024'd?
What are typical values of COL1? Is col1 numeric or character?
What are typical values of COL2? Is col2 numeric or character?

Please address all of these points, and not just one.

Re: How to pass literal date to where clause

ballardw — Thu, 16 May 2024 17:23:50 GMT

@current_thing wrote:

I know I can reformat as '01Jan24'd and that works, but I want to literally pass 01/01/24 without having to reformat. Does that make sense?

Not in SAS terms for SAS date values unless you are willing to put '01/01/24' as the argument of an INPUT function call such as

proc sql;
select * from temp where input('01/01/24',mmddyy10.) between col1 and col2;
quit;

or the DDMMYY10 informat, or whatever is needed to parse the string 01/01/24 which could be 1 Jan 2024 (or 1 Jan 1924 really should use 4-digit years for clarity) or 24 Jan 2001 (or 24 Jan 1901 or ... as I said use 4-digit years)

The INPUT function would expect character value in quotes. Without quotes your value will be treated as a numeric value of about 0.0416666667 (1 divided by 1 divided by 24) which is very unlikely to appear between date values unless Col1 is 1 Jan 1960 (0 in numeric terms) or earlier (negative numbers).

Re: How to pass literal date to where clause

Reeza — Thu, 16 May 2024 17:29:17 GMT

Assuming passing is a macro variable use the INPUT() function.

select * from temp where input("&macro_date", mmddyy10.) between col1 and col2;

If it's a variable you're reading in via a join that is character then use input and the variable name.

select * from temp where input(variable_date, mmddyy10.) between col1 and col2;

@current_thing wrote:

I know I can reformat as '01Jan24'd and that works, but I want to literally pass 01/01/24 without having to reformat. Does that make sense?

Re: How to pass literal date to where clause

ChrisHemedinger — Thu, 16 May 2024 17:31:53 GMT

Your query is comparing a date value -- which is essentially a number -- to a range defined by two other values. Since your example value (01/01/2024) is just a series of characters that SQL won't resolve as a date, you have to take a step to convert it to a number.

Maybe that value is coming to you from somewhere else and that's why you don't want to use the '01Jan2024'd date literal format. Instead you could convert it "inline" with a function that will yield a number:

%sysfunc(inputn(01/01/2024,mmddyy10))

Re: How to pass literal date to where clause

current_thing — Thu, 16 May 2024 17:34:20 GMT

that's it! thanks.