Solved

Converting Epoch time to Date in SAS

Posted on 2014-10-21
28
375 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
 
LVL 78

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 78

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 78

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now