Help using Base SAS procedures

SAS problem with Oracle dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

SAS problem with Oracle dates

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?


Accepted Solutions
Solution
‎09-26-2014 01:00 PM
Super User
Posts: 17,868

Re: SAS problem with Oracle dates

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. ;



View solution in original post


All Replies
Super User
Posts: 17,868

Re: SAS problem with Oracle dates

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.

Valued Guide
Posts: 3,208

Re: SAS problem with Oracle dates

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#p06jk0u30uhuj5n18f... (oracle)

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

---->-- ja karman --<-----
Occasional Contributor
Posts: 13

Re: SAS problem with Oracle dates

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 ?

Solution
‎09-26-2014 01:00 PM
Super User
Posts: 17,868

Re: SAS problem with Oracle dates

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. ;



Valued Guide
Posts: 3,208

Re: SAS problem with Oracle dates

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.

---->-- ja karman --<-----
Super User
Super User
Posts: 6,502

Re: SAS problem with Oracle dates

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 )

;

Occasional Contributor
Posts: 13

Re: SAS problem with Oracle dates

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 6701 views
  • 3 likes
  • 4 in conversation