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;
10037427 | CPE | 21NOV2003:16:42:06 | 30DEC4712:16:42:02 |
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;
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;
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.