Joining true date to text date in SAS/proc sql

Question

I need to join on ~today (or an arbitrary date in the past for checking) to a large lake based table using SAS/proc sql. I'm struggling because the lake table uses hardcoded text columns for dates. I initially wanted to reformat the text date as a date date but realised this would involve dealing with unnecessarily large amounts of data.

Looking at the information available there seems to be a lot of processes to change text to a date but not to change a formatted date to true text (when I use the put function it turns 2024-02-02 date into 2020 numeric).

Does anyone have any advice on how to look for further information?

Thanks

Put didn't work Cats worked but requires a macro to fill 0s. Feels like that's overkill?

Answer 1

I cannot understand what you are asking but this statement is false:

when I use the put function it turns 2024-02-02 date into 2020 numeric

When you use the PUT() or PUTN() function to format a numeric variable the result is always a character string. The PUT() function converts values into text. The input() function converts text into values.

Perhaps you made the mistake of trying to use 2020-02-03 in SAS code without wrapping it in quotes to let SAS know you meant a string literal. In that case you asked SAS to subtract 2 and 3 from 2020. Just like if you wrote 2+2 SAS would add them to get 4.

If you want to write a date constant in your code you can use a date literal. Which is a string that the DATE informat can understand enclosed in quotes and followed immediately by the letter D. So '03FEB2020'd for example. Or you could just write the unformatted number of days since 1960 that SAS uses to represent dates. The number 21948 would be third day of the second month of the year 2020.