BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ejohnson96
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

9 REPLIES 9
LinusH
Tourmaline | Level 20

What is the format of the UNIX timestamp?

Data never sleeps
ejohnson96
Calcite | Level 5

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
Barite | Level 11

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

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

ejohnson96
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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

 

 

 

Tom
Super User Tom
Super User

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
Calcite | Level 5

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

ChrisNZ
Tourmaline | Level 20

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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