Reading a date variable from Oracle table into sas dataset creating wrong values.

bhaskarkothavt — Mon, 13 Mar 2023 19:20:23 GMT

Hello Everyone,

I am reading a table from Oracle database into a SAS dataset which contains date variable. The value in the database table is '30-DEC-12' and when I checked in the sas dataset for a student the value is showing as '30DEC4712:00:00:00'. I dont understand why it is converting the year to 4712 and i tried yearcutoff option also which didnt make any changes, could someone help me if you know how resolve this issue

Below is the code i am using

options yearcutoff=1950;

%let date = '14-FEB-23'; /* >>> !!! Capture Date!!!*/

proc sql NOERRORSTOP EXEC;
connect to odbc (user=&userid password= &password1 dsn=robanner);
create table szrsmaj as select *
from connection to odbc
(select szrsmaj_pidm,
szrsmaj_levl_code,
szrsmaj_degc_code,
szrsmaj_coll_code,
szrsmaj_majr_code,
stvmajr_desc,
szrsmaj_dept_coac,
szrsmaj_from_date,
szrsmaj_to_date,
szrsmaj_activity_date
from stvmajr,
szrsmaj
where szrsmaj_majr_code = stvmajr_code(+)
and szrsmaj_from_date <= &date.
and szrsmaj_to_date >= &date.
order by szrsmaj_pidm,
szrsmaj_from_date desc);
quit;

proc print data = szrsmaj;
title 'sql extract as of 14 feb 23';
where szrsmaj_pidm = 10037427;
run;

Obs SZRSMAJ_PIDM SZRSMAJ_MAJR_CODE SZRSMAJ_FROM_DATE SZRSMAJ_TO_DATE6202

10037427

CPE

21NOV2003:16:42:06

30DEC4712:16:42:02

Re: Reading a date variable from Oracle table into sas dataset creating wrong values.

SASKiwi — Mon, 13 Mar 2023 19:56:54 GMT

YEARCUTOFF has no effect on Oracle date queries. ALWAYS use 4-digit years in queries to avoid any confusion:

%let date = '14-FEB-2023';

If those dates in SAS are wrong, what are they in Oracle for the same posted example?

Post the output from this:

proc contents data = szrsmaj;
run;

topic Re: Reading a date variable from Oracle table into sas dataset creating wrong values. in SAS Programming

Reading a date variable from Oracle table into sas dataset creating wrong values.

Re: Reading a date variable from Oracle table into sas dataset creating wrong values.