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
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.
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.
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
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
Still its not working. ...Any other way where it can be solved
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
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
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;
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.
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.
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!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.