datetime informat

[ejohnson96]

I have a a UNIX datetime variable, and am trying to convert it into first a SAS datetime variable, and then use informat/formats to convert it to the format of yyyy-mm-dd hh:mm:ss but am having some trouble. The original datetime number has 13 digits. 

My question is how to convert this 13 digit datetime from UNIX to SAS and then get it into the correct format?

I have read it in as numeric so I currently have a UNIXtimestamp variable that contains the 13 digits. Now I keep trying to convert it from UNIX to SAS and it's not working and it doesn't like my informat. 

[LinusH]

What is the format of the UNIX timestamp?

[ejohnson96]

I am reading in and importing raw data from the web and this is the original format of the timestamp variable:

1139643118358

That's an example of one of the observations.

It says that it is measured in number of milliseconds from midnight on January 1st, 1970 (which I read is a UNIX timestamp). And I want to change it to a variable that looks like YYYY-MM-DD HH:MM:SS but measured in SAS, so from midnight on January 1st, 1960.

[Tim_SAS]

Usage Note 39499: Convert a UNIX datetime to a SAS® datetime

http://support.sas.com/kb/39/499.html

[ejohnson96]

I copied this code but when I print out the data, the time variable still shows up just as numbers, not in the correct form. 

SAS_time = dhms('01jan1970'd,0,0, UNIX_time);
informat SAS_time datetime20.;

UNIX_time is my original time variable, and I want the SAS_time variable to be printed in the data set in the form of:

YYYY-MM-DD HH:MM:SS

[Tom]

You didn't attach any format to your new variable. That is why it is still printing as raw number of seconds.  There is no value in attaching an INFORMAT to value you are not reading from raw text data.

FORMAT SAS_DATETIME DATETIME20.;

[ejohnson96]

If I do that then it just prints out 20 *'s (like this: ********************). Not sure why this is happenning

[Tom · Accepted solution]

That is SAS's way of showing that the value is too large or too small to fit the format.  

Check your values and figure out what is happening.

If your number is number of seconds from 1/1/1970 then you can divide by 60*60*24*365.25 to get an estimate of the number of years since 1970 your number represents.  If result is not in between 40 and 50 then you do not have a timestamp that is in his decade.  If you get a number between 40,000 and 50,000 then you might check if your source time is number of milliseconds instead of seconds.

data x;
  input t;
  days=(t/1000)/(24*60*60);
  years=days/365.25 ;
  put (_all_) (= best32. / );
  dt = dhms('01JAN1970'D,0,0,t/1000);
  put dt= datetime20. ;
cards;
1139643118358
;

results

t=1139643118358
days=13190.3138698842
years=36.1131112111821
dt=11FEB2006:07:31:58

[ejohnson96]

It worked! Thanks so much for your help, it is greatly appreciated. 

[ChrisNZ]

Your number contains the milliseconds, the usage note doesn't.

data t;

unix_dtime=1139643118358;

Sas_dtime2 = dhms('01jan1970'd,0,0, unix_dtime/1000);

putlog SAS_dtime2 datetime22.3;

run;

11FEB2006:07:31:58.358