Michelle

I have added a bit of oracle and a bit of sas. The oracle shows how to select by finish time. To_date is an Oracle function to convert text to a date, the second parameter is a mask showing what format your date is in. Another mask is 'dd/mm/yyyy' for dates like 01/01/2003. You get the pattern. Because Oracle stores date as date time I have added the sas function datepart(...) to extract finish_time as a sas date discarding the time portion.

Philip

proc sql; connect to oracle(user='xxxxx' pass='xxxxx' path='xxxxx'); create table testdata as Select INDEXFIELD, datepart(FINISH_TIME), VAR1 from connection to oracle(select A1."INDEXFIELD", A1."FINISH_TIME", A1."VAR1" from DATA.TESTDATA where finish_time > to_date('01Jan2003','ddmonyyyy') ) as t1( INDEXFIELD, FINISH_TIME, VAR1) ;

Michelle Jellinghaus To: SAS-L@LISTSERV.UGA.EDU <michelle@EMODE.C cc: OM> Subject: SAS/SQL date field query Sent by: "SAS(r) Discussion" <SAS-L@LISTSERV.U GA.EDU>

05/02/2003 01:32 PM Please respond to Michelle Jellinghaus

When querying an Oracle database through SAS/SQL, what code would I write to get a dataset that includes only those observations that occur later than a particular date?

For example, let's say there's a table in our Oracle data warehouse called 'testdata' and it has a datetime field called 'finish_time'. If I want only those observations that have a 'finish_time' later than Jan 1, 2003, what would the WHERE statement look like? I don't see any way to do this by using the Query tool & have attempted a few where statements with little success using proc sql. You'll notice that the where statement below is, well, less than optimal ;)

Here's what I have so far:

proc sql; connect to oracle(user='xxxxx' pass='xxxxx' path='xxxxx'); create table testdata as Select INDEXFIELD, FINISH_TIME, VAR1 from connection to oracle(select A1."INDEXFIELD", A1."FINISH_TIME", A1."VAR1" from DATA.TESTDATA where /* help!?!?!*/ ) as t1( INDEXFIELD, FINISH_TIME, VAR1) ;

Any ideas would be greatly appreciated.

M