BookmarkSubscribeRSS Feed
Abraham
Obsidian | Level 7

Hello Everyone!

While connecting to Oracle database and pulling one variable data as DOB for the user King, I am getting null value. Can anyone help me.

e.g. Variable DOB ='01/09/1167'

proc sql;

        connect to oracle (user=scott orapw=tiger path=school);

            create table temp as

                Select * from connection to oracle

(SELECT dob

FROM   emp

where ename='KING';  /*value of DOB=01/09/1167*/)

disconnect from oracle;

quit;

Output:  null value(.)

Can I use Options year cut-off and if it used, other value are changing (as lakhs of records are there for retrieval from oracle db)

Thanks in advance

11 REPLIES 11
Tom
Super User Tom
Super User

SAS formats do not work with dates before 1582.

If you really need to process that as a valid date then you probably need to pull it apart on the Oracle side into Month,Day and Year fields as process it in SAS without using date formats.

art297
Opal | Level 21

The earliest date that SAS can handle is Jan 1, 1582.  If you are going to do this in SAS you'll have to download Year, Month and Day, separately from Oracle, load them into say as numbers (not dates), and then build your query based on whatever combination of those variables you might need.

Abraham
Obsidian | Level 7

Thank You Arthur and Tom for your guidance.

I tried in this way but still not able to succeed.

In oracle, I have splitted the date into three diff variables as day, month and year.

CREATE TABLE emp_dob AS

select distinct empid,dob,

extract(day from dob) as sday,

extract(month from dob) as smonth,

extract(year from dob) as syear

from emp

After that I used the same approach like below but still getting blank value

proc sql;

connect to oracle (user=scott orapw=tiger path=school);

            create table temp as

                Select * from connection to oracle

(SELECT dob

FROM   emp

where ename='KING');  /*value of DOB=01/09/1167*/

disconnect from oracle;

quit;

data temp;

set temp;

New_Date = mdy(sMonth, sDay, sYear);

format New_Date date9.;

run;

Thanks in advance

Ksharp
Super User

Or Did you try another connection way -libname.

libname x orcale path= .........  ;

proc sql;

SELECT dob

FROM   x.emp

where ename = 'KING' ;  /* strip(ename) = 'KING'   */

quit;

Message was edited by: xia keshan

Abraham
Obsidian | Level 7

Still its not working. ...Any other way where it can be solved

Ksharp
Super User

libname x orcale path= .........  ;

proc sql;

SELECT dob

FROM   x.emp

where ename contains 'KING' ; /* upcase(ename) contains 'KING'   */

quit;

And if you are familiar with Oracel SQL ,you can pass it through :

proc sql;

connect to oracle (user=scott orapw=tiger path=school);

            create table temp as

                Select * from connection to oracle

(

select distinct empid,dob,

extract(day from dob) as sday,

extract(month from dob) as smonth,

extract(year from dob) as syear

from emp


  );  /*value of DOB=01/09/1167*/

disconnect from oracle;

quit;

Message was edited by: xia keshan

Abraham
Obsidian | Level 7

Dear Ksharp,

I have no issue with connect to Oracle db.

My question is that when I retrieve date (01/09/1167) from oracle into SAS environement, I am getting null value. Please read my first discussion.

So Tom and Arthur  suggest me to split this type of date into day, month and year format. But still its not working.

Thanks in advance

Ksharp
Super User

That could not be happened. if year month day all are extracted and has right type.

libname x oracle ...

proc sql;

select sday, smonth , syear

from x. emp_dob

  where upcase(ename) = 'KING' ;

quit;



Kurt_Bremser
Super User

According to your code, you are still trying to import dob into SAS, instead of the the three variables for day, month, year. dob will never get you more than a missing value.

ballardw
Super User

Due to changes changes imposed by authorities the English date system, which SAS uses cannot uniquely determine a date prior to 1582. Anything involving years prior to that SAS will, for good and sound reasons, not create a SAS date value because SAS dates are offset values from a given day. There is no guarantee in most data sources as to which calendar is used prior to 1582 so the exact offset cannot be calculated.

Also any SAS created date formats applied to a variable with an offset that resolves to prior to 1582 will only display the ranges as BEST.

Karthikeyan
Fluorite | Level 6

Can you try pulling the DOB as a character string instead of a  date value ? I do this in DB2 , not sure if there's a similar function(Char())  in Oracle

CREATE TABLE emp_dob AS

select distinct empid,

    CHAR(dob)

from emp    ..  this is how I do it in DB2

Thanks!!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 4381 views
  • 0 likes
  • 7 in conversation