How to convert SAS E dates into readable dates

Question

Hi I'm working in SAS platform and I've a data_set with more then 30 columns. there are two date columns in that data-set. dates in that data set are in format as 1.33E12

This is the little part of my table

I want to create a new data-set with few columns and then I'm exporting it to excel file.

my code is

dataset
 othercolumns |  date1   |   date2
      -         1.33E12    2.53E14 

proc sql noprint;
 create table my_data_set as
 select ID, col_1, col_2, date1, date2
 from data_set;
quit;

I want my date values in date1 and date2 column in a readable format like 10feb2017 as date9. SAS date format so they can be exported to my excel file. right now with E power dates I'm getting ####### as date1 and date2 columns in excel

I've tried

select ID, col_1, col_2, datepaart(date1), datepart(date2)

Warning: Invalid Argument, getting '.' values in date column

select ID, col_1, col_2, date1 date9., date2 date9.
select ID, col_1, col_2, date1 DATEw., date2 DATEw.

Error: Syntax error

select ID, col_1, col_2, date1 format=DATE9., date2 format=DATE9.

Getting the same E date values in my table

select ID, col_1, col_2, put(date1 , date9. ), put(date2 ,  date9.)

Error: Date value out of range

How to convert the E date into a readable format into my table so i can export it to excel?

this is my export code

ods excel file ="C:\data.xlsx";
ods excel close;

proc export
  data = work.my_data_set
  dbms = xlsx
  outfile = "C:\data.xlsx"
  replace;
quit;

Please update your question to include a full reproducible example. — user667489, Jan 25 '18 at 09:03
What date does `2.53E14` represent? That's far too large even for a datetime variable, even *after* dividing by 1000 first to convert from milliseconds to seconds. — user667489, Jan 25 '18 at 10:42
Please update your question to include the corresponding human-readable dates that you are trying to produce from the sample data you've provided. — user667489, Jan 25 '18 at 11:03
@user667489 I want to update my values as 10feb2017 in SAS date9. format and I've updated my question. — Azeem112, Jan 25 '18 at 11:13
Are you saying that `2.53e14 = 10feb2017`? I'm not sure what date format you're trying to convert from. — user667489, Jan 25 '18 at 11:40
All i want is to convert that numeric value in date9. format. — Azeem112, Jan 25 '18 at 11:51
What are the data values shown when they are formatted with `BEST16.` ? Do you know where the date values came from ? — Richard, Jan 25 '18 at 13:17

score 1 · Answer 1 · answered Jan 25 '18 at 17:05

data have;
  unix_ts = 253402300799;
  put unix_ts= datetime21.;
  sas_dt = unix_ts + '01JAN1970:0:0'DT ;
  put sas_dt= datetime21.;
run;

proc sql;
  create table want as 
  select
  (
    case
      when unix_ts + '01JAN1970:0:0'DT > '27FEB8000:0:0'DT then unix_ts + '01JAN1970:0:0'DT - 2 * 86400
      when unix_ts + '01JAN1970:0:0'DT > '28FEB4000:0:0'DT then unix_ts + '01JAN1970:0:0'DT - 1 * 86400
      else unix_ts + '01JAN1970:0:0'DT 
    end 
  ) as sas_date format=datetime21.
  from have;
quit;

Rather than cutting and pasting you should understand what is going on with the case statement and the 01-JAN-1970

253,402,300,799

Unix timestamp, seconds from 01-JAN-1970, representing 31-DEC-9999:23:59:59
Likely sentinel value contained in valid_to that OP imprecisely shows as 2.534E14
Date columns presumed to be Unix time stamps.

253,717,747,199

SAS datetime value '31-DEC-9999:23:59:59'DT is seconds from 01-JAN-1960

Timestamp conversion

Unix timestamp values are epoch 01-JAN-1970:0:0:0
SAS datetime values are epoch 01-JAN-1960:0:0:0

So one would presume a SAS values are 10 years (in seconds) greater than Unix value.

The simple approach is to add the epoch base differential to the Unix timestamp to achieve the SAS datetime

SAS_DT = UNIX_TS + '01JAN1970'DT; *Naive conversion;

However, this is incorrect because Unix and SAS calendaring disagree on some leap years!

253,402,300,799 is 31-DEC-9999:23:59:59 per https://www.epochconverter.com/
253,717,747,199 is '31-DEC-9999:23:59:59'DT
difference, 315,446,400 should be SAS '01-JAN-1970:0:0'DT. But the difference is actually '30DEC1969:00:00'DT.
So, adding the epoch baseline differential to a far off Unix timestamp will result in a SAS datetime that does not represent the same calendar point as in Unix.
- In other words 253,402,300,799 + '01-JAN-1970:0:0'DT is '02-JAN-10000:0:0'DT -- two days beyond the expected Unix sentinel
- Or, after about 8,000 years, the calendar accounting systems in Unix and SAS will deviate by 2 days.

Calendaring deviation

Unix calendaring considers year 4000 to be a leap year, 29-FEB-4000 is valid.
SAS calendaring incorrectly considers 4000 to be a non-leap year, '29-FEB-4000'DT is invalid.

      ly4000 = '29-FEB-4000:0:0'DT;
               -------------------
               77
ERROR: Invalid date/time/datetime constant '29-FEB-4000:0:0'DT.
ERROR 77-185: Invalid number conversion on '29-FEB-4000:0:0'DT.

The same deviation happens again in year 8,000.

The least damaged conversion of Unix timestamp to SAS datetime takes the naïve conversion and subtracts one day for each misaligned leap-day determination in the time frame.

I think that most people are using those dates from the year 9999 as an upper bound to indicate time periods that are still ongoing. If you are worried about converting back and forth between SAS datetimes and Unix timestamps then perhaps you can use the year 3999 instead and avoid the leapyear issue. — Tom, Jan 25 '18 at 17:31

user667489 · Answer 2 · 2018-01-25T11:53:34.923

It looks as though you have two different types of UNIX timestamps, which count the number of milliseconds or microseconds from a particular date and time - usually 1st January 1970 00:00:00.000000. Without knowing exactly what sort of timestamps they are, I can only make an educated guess as to how to convert them to human-readable dates. Here are a few possible interpretations:

data example;
  date1=2.53e14;
  date2=1.33e12;
run;

proc sql;
create table want as 
select 
  intnx('year',datepart(date/1e3),10,'s') format = yymmdd10. as date1a, 
  intnx('year',datepart(date/1e6),10,'s') format = yymmdd10. as date1b, 
  intnx('year',datepart(date2/1e3),10,'s') format = yymmdd10. as date2a,
  intnx('year',datepart(date2/1e6),10,'s') format = yymmdd10. as date2b
from example;
quit;

The logic here is:

Divide by 1000 or 1000000 to convert to seconds
Interpret the result as a SAS datetime value counting the number of seconds from 1st January 1960 00:00:00
Extract the date component from the datetime
Add 10 years to convert to the UNIX epoch

Hopefully one of these looks right to you.

this format is giving me ************ in my date column of dataset — Azeem112, Jan 25 '18 at 08:58

How to convert SAS E dates into readable dates

2 Answers2

253,402,300,799

253,717,747,199

Timestamp conversion

Calendaring deviation