Date Problem

[gilsonr]

I am running a query against an Oracle database that stores dates in the following format 23SEP2004:00:00:00. When I try to select records for an specific day, in a where clause I dont get the correct results. I am using the Datepart function to get rid of the Hr:mm:ss.

Any ideas

 

[ChrisW75]

put your code up on the thread and we'll have a look. The following scenario should work though

proc sql;

connect to oracle as oracle
(user="&dbuser" pass="&dbpass" path="&dbpath" );

create table table1 as
select *
,datepart(mydate) as newdate
from connection to oracle
(select .....
from ....
)
;
quit;

data table2;
set table(where=(newdate = '14FEB2002'd));
....
run;

But it depends on what you are trying to do....

 

[dje]

I use code as follows for oracle sql requests

proc sql;
connect to ODBC(dsn=xx uid=xx pwd=xx );

create table work.t1 as
select * from connection to odbc(select *

from file_x a3

where a3.date > to_date('03-22-03','mm-dd-yy')
);

dje

 

[ChrisW75]

Ah! There's the problem, it's the Oracle side that's giving you the problem, not the SAS, you need to format the date a3.date to the same format as your check string, you are currently comparing a datetime quantity with a date (apples and oranges), you'll get no matches like this. Best thing to do is something like this:-

where to_char(a3.date,'dd/mm/yyyy') = '03/07/2003'

That should do the trick, it formats the date on the host system to the format you want to check against.
Enjoy.

 

[gilsonr]

Chrisw75 & dje thanks for the help

I initially tried this code

data valex.BG;
set cmn.Camt_promo_history;
keep contact_urn source_account response_code campaign_ID date_extracted ;
where substr(response_code,1,5, in ('XP87P','XP88P','XP89P','XP90P','XP92P','XP93P',
'XP94P','XP95P','XP91P','XP97P','XP98P','XP99P'))
and date_extracted ='13DEC2004'd;
run;

but this did not seem to work, therefore I amended this to the following

data valex.BG;
set cmn.Camt_promo_history;
keep contact_urn source_account response_code campaign_ID date_extracted ;
where (response_code = 'XP85PP' or
response_code = 'XP86PP' or
response_code = 'XP87PP' or
response_code = 'XP88PP' or
response_code = 'XP89PP' or
response_code = 'XP90PP' or
response_code = 'XP91PP' or
response_code = 'XP92PP' or
response_code = 'XP93PP' or
response_code = 'XP94PP' or
response_code = 'XP95PP' or
response_code = 'XP97PP' or
response_code = 'XP98PP')
and date_extracted='13DEC2004'd;
run;

and this works. I dont understand why, therefore I created the following Proc SQL

proc sql;
create table valex.BG as
select contact_urn,
source_account,
response_code,
campaign_ID,
date_extracted
from cmn.Camt_promo_history
where (response_code = 'XP85PP' or
response_code = 'XP86PP' or
response_code = 'XP87PP' or
response_code = 'XP88PP' or
response_code = 'XP89PP' or
response_code = 'XP90PP' or
response_code = 'XP91PP' or
response_code = 'XP92PP' or
response_code = 'XP93PP' or
response_code = 'XP94PP' or
response_code = 'XP95PP' or
response_code = 'XP97PP' or
response_code = 'XP98PP')
and date_extracted='13DEC2004'd
;
quit;
run;

and this worked OK.

Therefore is there a problem when SAS talks to Oracle, that I need to be aware of.

 

[ChrisW75]

When you say "It didn't work", in what way didn't it work? No rows returned? The wrong rows returned, error message etc?
If you have SAS/Connect, I would recommend using that rather than the ODBC connection.