Warm tip: This article is reproduced from stackoverflow.com, please click
oracle sas to-date julian-date

Oracle to_date with format J (Julian) in SAS

发布于 2020-03-29 12:47:07

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
Questioner
doremi
Viewed
82
Chris Long 2020-01-31 18:52

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.