Do you know somebody how to convert Oracle's numbers representing Julian dates to SAS? I have a table in SAS already, which has time_key
column. In Oracle, the conversion would be to_date(time_key, 'j')
where j
stands for Julian. Do you know how to do this in SAS?
SAS table example:
TIME_KEY
2456658
2456689
Expected output:
TIME_KEY_DATE
31DEC2013
31JAN2014
I don't know anything about Oracle's Julian date format, but it seems that it's just a number of days from some 'day 0', just like in SAS. The day 0 in SAS is 01JAN1960 so we just need to figure out the offset between Oracle's system, where 31DEC2013 is day number 2456658, and SAS's system, where 31DEC2013 is 22280:
data dates;
time_key = 24566658; output;
time_key = 24566689; output;
run;
* Calculate the offset, given 24566658 is 31-Dec-2013;
data _null_;
call symput("offset", 24566658 - "31DEC2013"d);
run;
%put Offset from SAS dates to Oracle dates is &offset days;
data converted;
set dates;
* Adjust the Oracle date values by subtracting the calculated offset;
sas_time_key_numeric = time_key - &offset;
sas_time_key = put(time_key - &offset, date9.);
put time_key= sas_time_key_numeric= sas_time_key=;
run;
The output is:
10 %put Offset from SAS dates to Oracle dates is &offset days;
Offset from SAS dates to Oracle dates is 24546935 days
11
12 data converted;
13 set dates;
14 sas_time_key_numeric = time_key - &offset;
15 sas_time_key = put(time_key - &offset, date9.);
16 put time_key= sas_time_key_numeric= sas_time_key=;
17 run;
time_key=24566658 sas_time_key_numeric=19723 sas_time_key=31DEC2013
time_key=24566689 sas_time_key_numeric=19754 sas_time_key=31JAN2014
Which gives the correct conversion.
So the magic number is 24546935; subtract that from your Oracle dates to get the corresponding SAS date value, then apply your desired date format.
Very cool solution, thanks!