Date:         Fri, 9 Apr 2010 12:50:50 -0500
Reply-To:     Sudo Ku <crafty876@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sudo Ku <crafty876@HOTMAIL.COM>
Subject:      SAS, Impromptu and date7. selection on a datetime field
Content-Type: text/plain; charset="iso-8859-1"

Hello:

Not sure I can really re-create my problem in this environment but let me try and explain...

The bulk of my work involves querying SQL server data warehouses and we rely quite heavily on dates. The format of the dates in our warehouses are often datetime with and most often the time is recorded as :00:00:00.000 when only a date is used.

In the case of these warehoused data I (and the other SAS programmers in our unit) have conventionally made our queries using date="01APR2007"d and have not needed to include the time. This where my problem begins because the fact that we can query a datetime field with only a date7 or date9 filter in our where statement does not function on SAS data sets, ie the following returns no rows:


data datetime;
input event dt datetime20.;
format dt datetime20.;
cards;
1    06APR2010:00:00:00
2    06APR2010:23:59:59
3    07APR2010:00:00:00
4    07APR2010:00:00:01
5    07APR2010:00:01:00
6    07APR2010:01:00:00
;

run;

proc sql; select * from datetime where dt<="07APR2010"D;
quit;

But if I do the following, all six rows will be returned.

proc sql; select * from datetime where datepart(dt)<="07APR2010"D;
quit;

Recently I have been working on converting some impromptu code from another department and I came across a situation where the impromtu query used a warehouse datetime field as part of it's filtering process and low and behold this time the time field was actually not all zeros but had acutal time. The impromtu query only used the date7. format and in it's case dt<="07APR2010"D it would have returned all 6 rows. My SAS query however only returned 3 rows... In as much as I can summarize:

Impromptu using the date7. query gave me all 07APR2010 events but SAS only gave me the 3rd event where the time was 00:00:00.000... What I would like to know is which program is right and which program is wrong? My opinion is that impromptu is correct and SAS should be giving me all of April 7th events...

I also realize I could query my warehouse data using the datetime function in my SQL step but this really slows down the SQL query and can lead to errors etc..

Regards,

Kevin





_________________________________________________________________
Hotmail & Messenger are available on your phone. Try now.
http://go.microsoft.com/?linkid=9724461