topic Re: DI Studio - Extract transformation - filtering by dates in SAS Data Management

DI Studio - Extract transformation - filtering by dates

Peter_Wine — Thu, 09 Nov 2017 08:58:37 GMT

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?

Re: SAS DI - Extract transformation - filtering by dates

Reeza — Wed, 08 Nov 2017 16:11:20 GMT

Date literals are in the Date9 Format:

My_Date = '08Nov2017'd;

Re: SAS DI - Extract transformation - filtering by dates

jklaverstijn — Wed, 08 Nov 2017 16:25:18 GMT

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.

Re: SAS DI - Extract transformation - filtering by dates

LinusH — Thu, 09 Nov 2017 09:02:20 GMT

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

Re: SAS DI - Extract transformation - filtering by dates

Peter_Wine — Thu, 09 Nov 2017 10:14:04 GMT

Hi Reeza, Thanks, it works! 🙂

Re: DI Studio - Extract transformation - filtering by dates

jklaverstijn — Thu, 09 Nov 2017 10:35:03 GMT

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