topic Re: Getting blank value for date in SAS Programming

Getting blank value for date

Abraham — Sun, 06 Apr 2014 11:56:27 GMT

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

Re: Getting blank value for date

Tom — Sun, 06 Apr 2014 14:56:00 GMT

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.

Re: Getting blank value for date

art297 — Sun, 06 Apr 2014 14:56:25 GMT

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.

Re: Getting blank value for date

Abraham — Mon, 07 Apr 2014 04:20:04 GMT

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

Re: Getting blank value for date

Ksharp — Mon, 07 Apr 2014 04:26:54 GMT

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

Re: Getting blank value for date

Abraham — Mon, 07 Apr 2014 05:00:40 GMT

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

Re: Getting blank value for date

Ksharp — Mon, 07 Apr 2014 05:26:13 GMT

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

Re: Getting blank value for date

Abraham — Mon, 07 Apr 2014 05:31:21 GMT

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

Re: Getting blank value for date

Ksharp — Mon, 07 Apr 2014 06:00:03 GMT

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;

Re: Getting blank value for date

Kurt_Bremser — Mon, 07 Apr 2014 06:42:27 GMT

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.

Re: Getting blank value for date

Karthikeyan — Mon, 07 Apr 2014 08:04:25 GMT

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

Re: Getting blank value for date

ballardw — Mon, 07 Apr 2014 16:08:17 GMT

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.