How to pass literal date to where clause

[current_thing]

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!

[PaigeMiller]

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.

[current_thing]

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?

[PaigeMiller]

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.

[ChrisHemedinger]

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))

[ballardw · Accepted solution]

---

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

[current_thing]

that's it! thanks.

[Reeza]

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?

---