topic Re: datetime informat in SAS Programming

datetime informat

ejohnson96 — Tue, 28 Jun 2016 16:59:31 GMT

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.

Re: datetime informat

LinusH — Tue, 28 Jun 2016 17:24:49 GMT

What is the format of the UNIX timestamp?

Re: datetime informat

ejohnson96 — Tue, 28 Jun 2016 17:27:39 GMT

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.

Re: datetime informat

Tim_SAS — Tue, 28 Jun 2016 17:44:38 GMT

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

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

Re: datetime informat

ejohnson96 — Tue, 28 Jun 2016 17:55:09 GMT

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

Re: datetime informat

Tom — Tue, 28 Jun 2016 18:02:03 GMT

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.;

Re: datetime informat

ejohnson96 — Tue, 28 Jun 2016 18:07:00 GMT

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

Re: datetime informat

Tom — Tue, 28 Jun 2016 18:27:08 GMT

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

Re: datetime informat

ejohnson96 — Tue, 28 Jun 2016 20:30:21 GMT

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

Re: datetime informat

ChrisNZ — Tue, 28 Jun 2016 22:24:02 GMT

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