DATA Step, Macro, Functions and more

Getting blank value for date

Reply
Contributor
Posts: 63

Getting blank value for date

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

Super User
Super User
Posts: 7,076

Re: Getting blank value for date

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.

PROC Star
Posts: 7,492

Re: Getting blank value for date

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.

Contributor
Posts: 63

Re: Getting blank value for date

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

Super User
Posts: 10,044

Re: Getting blank value for date

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

Contributor
Posts: 63

Re: Getting blank value for date

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

Super User
Posts: 10,044

Re: Getting blank value for date

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

Contributor
Posts: 63

Re: Getting blank value for date

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

Super User
Posts: 10,044

Re: Getting blank value for date

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;



Super User
Posts: 7,854

Re: Getting blank value for date

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: Getting blank value for date

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.

Contributor
Posts: 37

Re: Getting blank value for date

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!!

Ask a Question
Discussion stats
  • 11 replies
  • 1280 views
  • 0 likes
  • 7 in conversation