LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous (more recent) messageNext (less recent) messagePrevious (more recent) in topicNext (less recent) in topicPrevious (more recent) by same authorNext (less recent) by same authorPrevious page (February 2003, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Wed, 5 Feb 2003 16:14:06 +1100
Reply-To:   Philip_Crane@WORKCOVER.VIC.GOV.AU
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   Philip_Crane@WORKCOVER.VIC.GOV.AU
Subject:   Re: SAS/SQL date field query
Comments:   To: Michelle Jellinghaus <michelle@EMODE.COM>
Content-type:   text/plain; charset=us-ascii

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


Back to: Top of message | Previous page | Main SAS-L page