Solved

Converting Epoch time to Date in SAS

Posted on 2014-10-21
28
481 Views
Last Modified: 2016-02-13
Hello Experts,

Can someone please tell me how to convert epoch time to human date in either SAS proc Sql or SAS data steps.

Thanks
0
Comment
Question by:fb1990
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 11
  • 3
28 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40397424
I think the simplest method would be to add ten years' worth of seconds to your epoch time and then save it as a SAS datetime value.
0
 
LVL 1

Author Comment

by:fb1990
ID: 40397526
can you please demonstrate by example?

Thanks,
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40397697
Try adding a datetime value at the unix epoch (1/1/1970) to your epoch time value.  You can then use it as a SAS datetime value.

This should be equivalent to adding the dhms('01jan1970'd,0,0,0) value
or adding '01JAN1970:00:00:00'dt value

Reference:
http://support.sas.com/kb/39/499.html
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 81

Expert Comment

by:David Johnson, CD, MVP
ID: 40398423
data _null_;

   input dt $1-24 sec1970;

   unixEpoch = dhms('01jan1970'd,0,0,0);

   GMTsasDT = sec1970 + unixEpoch;

   PDTsasDT = GMTsasDT - dhms(0,7,0,0);

   put dt= (GMT: PDT:) (=datetime.) sec1970=;

   cards;

Open in new window

http://bit.ly/1yn1x2Z
0
 
LVL 1

Author Comment

by:fb1990
ID: 40403403
Hi aikimark,

While i was research how solve this epoch to human date problem, i came across the solution in the link that you provided above, it does not convert my epochtime to human date.  Rather it converts to a set of numbers not the date as i prefer.  Do you know anything that can be done to make is a readable date?

I looked up the solution giving by David Johnson as well.  No popping of the champagne yet..

Thanks for your time.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40403439
What are examples of epoch datetime values in your data and what are the correctly translated datetime values?
0
 
LVL 1

Author Comment

by:fb1990
ID: 40403896
Thanks aikimark.  I am attaching my sample data here.  epochtime is a text field.  I have an access query that can convert the to datetimes and date as shown in the attached data, but my dataset is too large for ms access.

I appreciate your assistance so far.
qrySample2.txt
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40403912
my dataset is too large for ms access
You can do your epoch time conversion in VBA, reading and writing the lines.  You don't have to store all the data in Access.

What kind of field is in your SAS dataset?  My answer assumed a numeric value.  The simple answer is to cast/format the text digits into a number as part of the conversion process.

What is the SAS code you used to read this text file?  This conversion should probably take place as the data is brought into the SAS dataset.
0
 
LVL 81

Expert Comment

by:David Johnson, CD, MVP
ID: 40404081
epoch time doesn't convert using a unix time converter and you are getting the date and time in human readable format.
0
 
LVL 81

Expert Comment

by:David Johnson, CD, MVP
ID: 40404084
the epoch time is too large to be from unix time and it is in human readable format if you change the |'s to spaces
custid  epochtime  datetimes  date
511  1413595491062  10/17/2014 21:24  10/17/2014
511  1413563812690  10/17/2014 12:36  10/17/2014
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40404124
This is a scaled integer value.  The actual datetime value should = 1/100 of these values
Example:
1413595491062 = 1413595491.062 = Sat, 18 Oct 2014 01:24:51 GMT
1413563812690 = 1413563812.690 = Fri, 17 Oct 2014 16:36:52 GMT
0
 
LVL 1

Author Comment

by:fb1990
ID: 40404215
here is the code that i am using:
proc import out= work.cau datafile= "T:\qrySample2.csv" dbms=dlm replace; delimiter='|; getnames=yes; datarow=2;run;

data test;
set cau;
Sas_dtime2 = dhms('01jan1970'd,0,0, focal_point);                                                                                       
   format SAS_dtime2 datetime20.; run;

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40404232
I think I spotted a syntax error in the import statement.  Your delimiter spec should be:
delimiter='|'

Open in new window

0
 
LVL 1

Author Comment

by:fb1990
ID: 40404332
that's true.  I have corrected in my code....
0
 
LVL 1

Author Comment

by:fb1990
ID: 40421256
hello Experts,

can someone please help solve this problem?

Thanks
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40421292
Try this:
proc import out= work.cau datafile= "T:\qrySample2.csv" dbms=dlm replace; delimiter='|'; getnames=yes; datarow=2;run;

data test;
set cau;
unixEpoch = dhms('01jan1970'd,0,0,0);
Sas_dtime2 = dhms(unixEpoch + (focal_point / 100));
format SAS_dtime2 datetime20.; 
run;

Open in new window

0
 
LVL 1

Author Comment

by:fb1990
ID: 40421339
i got this error message
38   data test;
39   set cau;
40   unixEpoch = dhms('01jan1970'd,0,0,0);
41   Sas_dtime2 = dhms(unixEpoch + (focal_point / 100));
                  ----
                  71
ERROR 71-185: The DHMS function call does not have enough arguments.

42   format SAS_dtime2 datetime20.;
43   run;

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40421344
oops
proc import out= work.cau datafile= "T:\qrySample2.csv" dbms=dlm replace; delimiter='|'; getnames=yes; datarow=2;run;

data test;
set cau;
unixEpoch = dhms('01jan1970'd,0,0,0);
Sas_dtime2 = dhms(unixEpoch + (focal_point / 100),0,0,0);
format SAS_dtime2 datetime20.; 
run;

Open in new window

0
 
LVL 1

Author Comment

by:fb1990
ID: 40422593
i ran the suggested by aikimark.  The code ran, but Sas_dtime2 is empty with .
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40423210
What type of data is focal_point?
0
 
LVL 1

Author Comment

by:fb1990
ID: 40423682
it is string currently
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40424267
how did the epochtime field become focal_point?
0
 
LVL 1

Author Comment

by:fb1990
ID: 40424461
oh.  I pulled the data from another data and trying to use SAS to mind and write reports based on the extract.  But when the data is imported into SAS, it changed to string based on the length of the field...
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40424535
what is the datatype of the epochtime field?
change the code to use the epochtime field.
0
 
LVL 1

Author Comment

by:fb1990
ID: 40424610
currently it the focal_point and the datatype is string to keep the data from truncating.  The length is 14... i am thinking i need to convert the data to int/numeric to be able to apply the solution you have suggested..
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40424663
it needs to be a floating point data type if you want to see the time component down to its maximum resolution.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Invest in your employees with these five simple steps to improve employee engagement and retention.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question