DI Studio - Extract transformation - filtering by dates

[Peter_Wine]

Hi All, I want to make a job in SAS DI Studio. Very simple transformation. I've got SAS table and want to use "Extract" transformation to extract data from it. In tab "Where" in Extract transformation I want to filter records by column "My_Date" which is in format yymmdd10. Conditions: -> My_Date = 2017-11-08 -> My_Date = '2017-11-08'd. -> My_Date = "2017-11-08"d. don't work. What is the proper condition?

[Reeza · Accepted solution]

Date literals are in the Date9 Format:

My_Date = '08Nov2017'd;

[Peter_Wine]

Hi Reeza, Thanks, it works! 🙂

[jklaverstijn]

To expand on @Reeza's correct response: date, time and datetime values are never stored "in a format". They are always a number counting days or seconds from 01JAN1970. The format is merely an indicator that tells SAS how to present the value and this can be changed at will. So the format does not dictate how to formulate a constant in the WHERE clause. It is always as @Reeza explained.

Regards,

- Jan.

[LinusH]

Being bit picky, but it's from 01Jan1960....

[jklaverstijn]

You're not picky at all @LinusH. These details make all the difference. I stand corrected. 33 year of SAS just doesn't outweigh 40 years of Unix :-).