Solved

Converting Epoch time to Date in SAS

Posted on 2014-10-21
28
436 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 80

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 80

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 80

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cognitive Services 1 13
SQL SELECT query help 7 56
Coldusion - DATA insert syntax problem 12 49
printf performancy 11 72
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

828 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