topic Re: SAS problem with Oracle dates in SAS Procedures

SAS problem with Oracle dates

nikhilbajaj — Fri, 26 Sep 2014 02:42:38 GMT

I am having problems with a very simple SQL query in SAS

rsubmit;
proc sql;
create table TEST_RESULT as
select CAL_D
from EDT01371_DATE

except

select CAL_D
from CSTDM011_DATE;
select count(*) from TEST_RESULT;

Here source is db2 and target is oracle. They both are same but this query is still not giving 0 as result just because in SAS, the value in target is looking different than what it is actually in the orcale table.

Data in orcale looks like - '24-APR-08'

But the same value in SAS looks like - '24APR2008:00:00:00'

Can someone provide a solution to my problem?

Re: SAS problem with Oracle dates

Reeza — Fri, 26 Sep 2014 02:50:45 GMT

Oracle typically uses datetime while SAS has numeric variables that can be date or datetime variables which explains why the dates show as different, but I'm not clear as to how that comes into your question.

Re: SAS problem with Oracle dates

jakarman — Fri, 26 Sep 2014 06:53:30 GMT

The query is simple but your are mentioning a conversion for Oracle to db2 using sas.

That are a lot of converions being involved. I do not see all those being defined (oracle db2).

The SAS date conversion is a scuccesful one. Get the documentational (access datatypes) background by:

http://support.sas.com/documentation/cdl/en/acreldb/67473/HTML/default/viewer.htm#p06jk0u30uhuj5n18fqw9sxr25lk.htm (oracle)

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition (db2)

Re: SAS problem with Oracle dates

nikhilbajaj — Fri, 26 Sep 2014 15:33:27 GMT

Ok So I think I was not very clear on my question.

There is no conversion happening here. I have imported data for both source and target in SAS and am now comparing them in SAS. But while importing data from oracle to SAS, one of the field values got changed. In orcale I had date, but after coming to SAS it became Timestamp.

Why is this happening ?

Re: SAS problem with Oracle dates

jakarman — Fri, 26 Sep 2014 16:14:08 GMT

The conversion are a result of different conventions at each environment.

Recognizing a data type for the several recognized types (see previous links) it will convert to the other side and adjust accordingly. It does that because of the meaning of the fields, you are not interested in the bit-bytes to interpreted them to symbols like the machines are trying to do that for you.

Re: SAS problem with Oracle dates

Reeza — Fri, 26 Sep 2014 17:00:03 GMT

Oracle stores its date types as Date Times but shows them as dates:

The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).

Oracle Data Types

SAS has two different date types, dates and date times. Since the Oracle value matches the SAS date time value it shows it as a date time value.

You can format it to show differently if you wish, using the appropriate SAS format or convert it to an actual date value using the datepart() function.

e.g. of formatting to look like a date:

format datevar dtdate9. ;

Re: SAS problem with Oracle dates

Tom — Fri, 26 Sep 2014 18:15:49 GMT

Sounds like you are comparing dates to datetimes.

You could apply the DATEPART() function to convert the datetime values from Oracle into dates.

create table from_oracle as

select datepart(cal_d) format = date9.

from connection to oracle (select cal_d from myschema.mytable )

;

create table from_db as

select cal_d format = date9.

from connection to db2 (select cal_d from mydb.mytable )

;

Re: SAS problem with Oracle dates

nikhilbajaj — Fri, 26 Sep 2014 19:59:24 GMT

So this is my final query-

rsubmit;
proc sql;
create table TEST_RESULT as
select put(CAL_D,date9.)
from EDT01371_DATE
where CAL_Y > 2007
and CAL_Y < 2016

except all

select put(CAL_D,dtdate9.)
from CSTDM011_DATE
where D_DIM_ID_N not in (0,1,2);
select count(*) from TEST_RESULT;

Thank you all for the efforts