DATA Step, Macro, Functions and more

datetime informat

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

datetime informat

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. 


Accepted Solutions
Solution
‎07-08-2016 05:32 PM
Super User
Super User
Posts: 6,495

Re: datetime informat

[ Edited ]

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

 

View solution in original post


All Replies
Super User
Posts: 5,254

Re: datetime informat

What is the format of the UNIX timestamp?

Data never sleeps
Occasional Contributor
Posts: 19

Re: datetime informat

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:MMSmiley FrustratedS but measured in SAS, so from midnight on January 1st, 1960.

Super Contributor
Posts: 394

Re: datetime informat

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

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

Occasional Contributor
Posts: 19

Re: datetime informat

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:MMSmiley FrustratedS

Super User
Super User
Posts: 6,495

Re: datetime informat

[ Edited ]

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

 

Occasional Contributor
Posts: 19

Re: datetime informat

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

 

 

 

Solution
‎07-08-2016 05:32 PM
Super User
Super User
Posts: 6,495

Re: datetime informat

[ Edited ]

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

 

Occasional Contributor
Posts: 19

Re: datetime informat

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

PROC Star
Posts: 1,555

Re: datetime informat

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

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 576 views
  • 0 likes
  • 5 in conversation