BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nikhilbajaj
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
Reeza
Super User

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.

jakarman
Barite | Level 11

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 --<-----
nikhilbajaj
Calcite | Level 5

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 ?

Reeza
Super User

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



jakarman
Barite | Level 11

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 --<-----
Tom
Super User Tom
Super User

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 )

;

nikhilbajaj
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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