Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

Converting Epoch time to Date in SAS

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
fb1990
Asked:
fb1990
  • 12
  • 11
  • 3
1 Solution
 
aikimarkCommented:
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
 
fb1990Author Commented:
can you please demonstrate by example?

Thanks,
0
 
aikimarkCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
David Johnson, CD, MVPOwnerCommented:
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
 
fb1990Author Commented:
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
 
aikimarkCommented:
What are examples of epoch datetime values in your data and what are the correctly translated datetime values?
0
 
fb1990Author Commented:
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
 
aikimarkCommented:
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
 
David Johnson, CD, MVPOwnerCommented:
epoch time doesn't convert using a unix time converter and you are getting the date and time in human readable format.
0
 
David Johnson, CD, MVPOwnerCommented:
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
 
aikimarkCommented:
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
 
fb1990Author Commented:
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
 
aikimarkCommented:
I think I spotted a syntax error in the import statement.  Your delimiter spec should be:
delimiter='|'

Open in new window

0
 
fb1990Author Commented:
that's true.  I have corrected in my code....
0
 
fb1990Author Commented:
hello Experts,

can someone please help solve this problem?

Thanks
0
 
aikimarkCommented:
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
 
fb1990Author Commented:
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
 
aikimarkCommented:
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
 
fb1990Author Commented:
i ran the suggested by aikimark.  The code ran, but Sas_dtime2 is empty with .
0
 
aikimarkCommented:
What type of data is focal_point?
0
 
fb1990Author Commented:
it is string currently
0
 
aikimarkCommented:
how did the epochtime field become focal_point?
0
 
fb1990Author Commented:
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
 
aikimarkCommented:
what is the datatype of the epochtime field?
change the code to use the epochtime field.
0
 
fb1990Author Commented:
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
 
aikimarkCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 12
  • 11
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now