3

I have a SAS dataset containing a datetime variable. I have ported this dataset into R using the sas7bdat package but the datetime variable is shown in integer number format (e.g. 1706835972). Is there any way to convert this integer into a date format?

Giorgio Spedicato
  • 2,413
  • 3
  • 31
  • 45
  • That looks like a UNIX timestamp. Try [Convert UNIX epoch to Date object in R](http://stackoverflow.com/questions/13456241/convert-unix-epoch-to-date-object-in-r) – user1477388 Jun 25 '14 at 16:16
  • 1
    SAS uses seconds since Jan 1, 1960 so try this: `as.POSIXct(1706835972, origin = "1960-01-01")` Also `convertDateTime` in Hmisc converts SAS datetimes. If you have some values from SAS for which you know the right answer we could double check these. – G. Grothendieck Jun 25 '14 at 16:25
  • In pairs, left value right formatted: x=1706835972 Formatted: 01FEB14:01:06:12 x=1716835972 Formatted: 27MAY14:18:52:52 x=1726835972 Formatted: 20SEP14:12:39:32 x=1736835972 Formatted: 14JAN15:06:26:12 x=1746835972 Formatted: 10MAY15:00:12:52 x=1756835972 Formatted: 02SEP15:17:59:32 x=1766835972 Formatted: 27DEC15:11:46:12 x=1776835972 Formatted: 21APR16:05:32:52 x=1786835972 Formatted: 14AUG16:23:19:32 x=1796835972 Formatted: 08DEC16:17:06:12 x=1806835972 Formatted: 03APR17:10:52:52 x=1816835972 Formatted: 28JUL17:04:39:32 x=1826835972 Formatted: 20NOV17:22:26:12 – Joe Jun 25 '14 at 20:13
  • Also note that SAS basic time values are not timezoned, so the above should be UTC/GMT values, not local time zone specific. – Joe Jun 25 '14 at 20:18

1 Answers1

7

To match exactly what SAS outputs for default datetime structure, you need to use as.POSIXct as was mentioned in comments, and additionally use the tz=UTC argument:

sasDateTimes <- c(1706835972, 1716835972, 1726835972, 1736835972, 1746835972, 
1756835972, 1766835972, 1776835972, 1786835972, 1796835972, 1806835972, 
1816835972, 1826835972, 1836835972, 1846835972, 1856835972, 1866835972, 
1876835972, 1886835972, 1896835972, 1906835972, 1916835972, 1926835972, 
1936835972, 1946835972, 1956835972, 1966835972, 1976835972, 1986835972, 
1996835972)

rPOSIX <- as.POSIXct(sasDateTimes,origin='1960-01-01',tz="UTC")
rPOSIX
 [1] "2014-02-01 01:06:12 UTC" "2014-05-27 18:52:52 UTC" "2014-09-20 12:39:32 UTC" "2015-01-14 06:26:12 UTC" "2015-05-10 00:12:52 UTC" "2015-09-02 17:59:32 UTC"
 [7] "2015-12-27 11:46:12 UTC" "2016-04-21 05:32:52 UTC" "2016-08-14 23:19:32 UTC" "2016-12-08 17:06:12 UTC" "2017-04-03 10:52:52 UTC" "2017-07-28 04:39:32 UTC"
[13] "2017-11-20 22:26:12 UTC" "2018-03-16 16:12:52 UTC" "2018-07-10 09:59:32 UTC" "2018-11-03 03:46:12 UTC" "2019-02-26 21:32:52 UTC" "2019-06-22 15:19:32 UTC"
[19] "2019-10-16 09:06:12 UTC" "2020-02-09 02:52:52 UTC" "2020-06-03 20:39:32 UTC" "2020-09-27 14:26:12 UTC" "2021-01-21 08:12:52 UTC" "2021-05-17 01:59:32 UTC"
[25] "2021-09-09 19:46:12 UTC" "2022-01-03 13:32:52 UTC" "2022-04-29 07:19:32 UTC" "2022-08-23 01:06:12 UTC" "2022-12-16 18:52:52 UTC" "2023-04-11 12:39:32 UTC"

Those will perfectly match what SAS displays in the default format (DATETIME17.). Output from SAS:

x=1706835972, Formatted: 01FEB14:01:06:12
x=1716835972, Formatted: 27MAY14:18:52:52
x=1726835972, Formatted: 20SEP14:12:39:32
x=1736835972, Formatted: 14JAN15:06:26:12
x=1746835972, Formatted: 10MAY15:00:12:52
x=1756835972, Formatted: 02SEP15:17:59:32
x=1766835972, Formatted: 27DEC15:11:46:12
x=1776835972, Formatted: 21APR16:05:32:52
x=1786835972, Formatted: 14AUG16:23:19:32
x=1796835972, Formatted: 08DEC16:17:06:12
x=1806835972, Formatted: 03APR17:10:52:52
x=1816835972, Formatted: 28JUL17:04:39:32
x=1826835972, Formatted: 20NOV17:22:26:12
x=1836835972, Formatted: 16MAR18:16:12:52
x=1846835972, Formatted: 10JUL18:09:59:32
x=1856835972, Formatted: 03NOV18:03:46:12
x=1866835972, Formatted: 26FEB19:21:32:52
x=1876835972, Formatted: 22JUN19:15:19:32
x=1886835972, Formatted: 16OCT19:09:06:12
x=1896835972, Formatted: 09FEB20:02:52:52
x=1906835972, Formatted: 03JUN20:20:39:32
x=1916835972, Formatted: 27SEP20:14:26:12
x=1926835972, Formatted: 21JAN21:08:12:52
x=1936835972, Formatted: 17MAY21:01:59:32
x=1946835972, Formatted: 09SEP21:19:46:12
x=1956835972, Formatted: 03JAN22:13:32:52
x=1966835972, Formatted: 29APR22:07:19:32
x=1976835972, Formatted: 23AUG22:01:06:12
x=1986835972, Formatted: 16DEC22:18:52:52
x=1996835972, Formatted: 11APR23:12:39:32

If the data was entered with time zone corrections into SAS, you should leave out the tz argument. In my experience, however, SAS datetimes usually are not stored respective to a particular timezone. Whether they are or not is not normally stored as part of the dataset, unfortunately; you might be able to look at the format that was used on the dataset (if it is one of the timezone displaying formats, odds are it does include timezones), but even that is not a guarantee. You would have to verify with the data source whether the datetimes are relative to local datetime (ie, # of seconds from 1/1/1960 local time midnight, or # of seconds from 1/1/1960 UTC midnight).

Joe
  • 62,789
  • 6
  • 49
  • 67