How to convert unix time stamp to SAS time stamp

How to convert unix time stamp to SAS time stamp

Post by Lilia » Tue, 06 Jan 2009 12:45:28


Hi there,
I'm new to this group and very much a newbie to SAS. I'm having
trouble trying to display unix time stamp to SAS time stamp. can
anyone help?

Basically, I have a data source that inputs a 20. numeric unix time
stamp and in my target SAS table i want it to be datetime. format but
obviously since SAS epoc time is 1/1/1960 and unix epoc time to
1/1/1970, the datetime outformat came out as ****** (unreable).

Thanks!

Lil.
 
 
 

How to convert unix time stamp to SAS time stamp

Post by jfh » Tue, 06 Jan 2009 13:46:38

You could subtract 315,597,600 from your Unix timestamp value to
obtain a SAS datetime value.

I suspect there's something else wrong. The values for a Unix
timestamp (number of seconds since 01jan1970) and a SAS datetime value
(number of seconds since 01jan1960) are of roughly the same magnitude,
so the displayed value will be off by 10 years, but nevertheless a
datetime should be displayed. If you're seeing ******, your values
may be out of range for some reason (for example, a misplaced decimal
point). I'm not sure how you would get 6 asterisks - the minimum
width for the datetime format is 7.

Posting sample code and values might be helpful.

 
 
 

How to convert unix time stamp to SAS time stamp

Post by gerhard.he » Tue, 06 Jan 2009 21:56:01

What I think is, that the format is either wrong, or much too short. Which
format did you use to display the values?
Try datetime20. in SAS and you should see something. Maybe that's wrong,
but after that you can convert it as Jack mentioned.
Gerhard


On Sun, 4 Jan 2009 20:46:38 -0800, Jack Hamilton < XXXX@XXXXX.COM >
 
 
 

How to convert unix time stamp to SAS time stamp

Post by Lilia » Wed, 07 Jan 2009 07:53:50

Thanks for the quick responses. I've tried Jack's suggestion and
subtracted 315,597,600 from the unix time stamp and had the format set
to DateTime. The resulting data still came up in asterisks. Sorry for
my bad example before, it was roughly 16 * and not just 7. Here is a
snipped of the code (generated from DI studio in the Table Loader
step):

%macro etls_loader;

%let SYSOPT = ;

/*---- Map the columns ----*/
proc datasets lib = work nolist nowarn memtype = (data view);
delete mapped;
quit;

%put %str(NOTE: Mapping columns ...);
proc sql;
create view work.mapped as
select rumId length = 8,
clientIP length = 8,
applicationID length = 8,
transactionID length = 8,
(timeStamp - 315597600) as timeStamp length = 8 format
= datetime. informat = 20.,
sessionStartTime length = 8,
netTime length = 8,
grossTime length = 8,
connectTime length = 8,
networkTime length = 8,
retransmissionTime length = 8,
serverTime length = 8,
sslTime length = 8,
clientTime length = 8,
serverTimeToFirstBuffer length = 8,
transactionSize length = 8,
numOfComponents length = 8,
loginName length = 301,
transName length = 601,
serverName length = 151
from &etls_lastTable;
quit;

I have yet to try Gerhard's suggestion of having datetime20. Will do
that now.
 
 
 

How to convert unix time stamp to SAS time stamp

Post by iebupdt » Wed, 07 Jan 2009 08:04:50

Can you show some of the values with NO datetime formatting so we can
see the actual values. That may help determine what is actually
broken.
 
 
 

How to convert unix time stamp to SAS time stamp

Post by Lilia » Wed, 07 Jan 2009 08:12:47

Tried Gerhard's way, same with the asterisks. An example of the
timeStamp value in it's original form from the mysql datasource is
1229979606474

So far, what i've tried are:
1. use timestamp - 3155976000 and let format set to datetime.
2. use timestamp - 3155976000 and let format set to datetime20.
3. set format to datetime.
4. set format to datetime20.

All of the above gives me a display of asterisks.
 
 
 

How to convert unix time stamp to SAS time stamp

Post by barry.a.sc » Wed, 07 Jan 2009 09:06:34

11AUG08:06:00:00 (48+ years after the start of the epoch) is
approximately 1.5E9. Your value is 1.2E12, approximately 1,000 times
further away from the start of the epoch or close to AD 48,000. My
Language Concepts manual says AD 19,900 is as far as you can go. I
would guess that sql is giving you something other than seconds.

Your value of 3155976000 prints as 03JAN2060:12:00:00 using datetime20.
on my 9.1.3 system.

-----Original Message-----
From: Lilian
Sent: Monday, January 05, 2009 3:13 PM
To: XXXX@XXXXX.COM
Subject: Re: How to convert unix time stamp to SAS time stamp

Tried Gerhard's way, same with the asterisks. An example of the
timeStamp value in it's original form from the mysql datasource is
1229979606474

So far, what i've tried are:
1. use timestamp - 3155976000 and let format set to datetime.
2. use timestamp - 3155976000 and let format set to datetime20.
3. set format to datetime.
4. set format to datetime20.

All of the above gives me a display of asterisks.
 
 
 

How to convert unix time stamp to SAS time stamp

Post by Lilia » Wed, 07 Jan 2009 09:17:44

1231200312179 translates to 1/6/2009 11:05:12
 
 
 

How to convert unix time stamp to SAS time stamp

Post by NordlD » Wed, 07 Jan 2009 09:44:58

> -----Original Message-----

Lilian,

this should convert your timestamp, which seems to be 1000-ths of a second since 01jan1970.

Sas_datetime = dhms('01jan1970'd,0,0,timestamp/1000);

Hope this is helpful,

Dan

Daniel J. Nordlund
Washington State Department of Social and Health Services
Planning, Performance, and Accountability
Research and Data Analysis Division
Olympia, WA 98504-5204
 
 
 

How to convert unix time stamp to SAS time stamp

Post by barry.a.sc » Wed, 07 Jan 2009 09:47:50

Not possible. An odd number of seconds after midnight will always yield
and odd value for seconds in the time. In SAS
06jan2009:11:05:12 is 1546859112
01jun2009:11:05:12 is 1559473512

Both numbers are three orders of magnitude smaller than your value. Is
sql giving you milliseconds instead of seconds?

-----Original Message-----
From: Lilian
Sent: Monday, January 05, 2009 4:18 PM
To: XXXX@XXXXX.COM
Subject: Re: How to convert unix time stamp to SAS time stamp

1231200312179 translates to 1/6/2009 11:05:12
 
 
 

How to convert unix time stamp to SAS time stamp

Post by NordlD » Wed, 07 Jan 2009 10:10:19

> -----Original Message-----

I probably should have pointed out that the Unix timestamp is relative to 01jan1970 00:00:00 UTC. Lilian appears to be in a UTC+11 timezone, and so the conversion should be

Sas_datetime = dhms('01jan1970'd,11,0,timestamp/1000);

Hope this is more helpful,

Dan

Daniel J. Nordlund
Washington State Department of Social and Health Services
Planning, Performance, and Accountability
Research and Data Analysis Division
Olympia, WA 98504-5204
 
 
 

How to convert unix time stamp to SAS time stamp

Post by Lilia » Wed, 07 Jan 2009 10:14:05

Hi Barry, you are right, MySQL is using milliseconds from 1/1/1970.
And my apologies again, in AUS time 1231200312179 translates to
6/1/2009 11:05:12 (I had american time in the previous post).

I tried Dan's way with using dhms('01jan1970'd,0,0,timeStamp/1000)and
format of dateTime. the dates are showing now, however it seems 'off'
by 11 hours. for example: 1231203455893 should be translated to
6/1/2009 11:57:35. Instead I get 6/1/2009 00:57:35.

Getting close... on another note, what exactly does dhms() function
do? Sorry a bit new with SAS.
 
 
 

How to convert unix time stamp to SAS time stamp

Post by Lilia » Wed, 07 Jan 2009 10:16:48

Just a thought, I'm guessing since I'm in AUS, we're GMT +10 and +1
day light savings at the moment. How do I encorporate that and make it
versatile enough when day light saving ends? Or is it link to the
server base SAS is installed?
 
 
 

How to convert unix time stamp to SAS time stamp

Post by snoopy36 » Wed, 07 Jan 2009 13:05:12

Lillian, you may have missed Dan's update, here it is again. The second
argument is the UTC correction.

< quote from Dan Nordlund>
I probably should have pointed out that the Unix timestamp is relative to
01jan1970 00:00:00 UTC. Lilian appears to be in a UTC+11 timezone, and so
the conversion should be

Sas_datetime = dhms('01jan1970'd,11,0, timestamp/1000);

Hope this is more helpful,

< / quote >

-Joe
 
 
 

How to convert unix time stamp to SAS time stamp

Post by Lilia » Wed, 07 Jan 2009 13:16:48

Hi Dan,

That worked! Thank you very much! So to clarify that means each time
when we have a change in daylight saving we'll have to just update the
+10 or +11 to the hour.

Can anyone tell me how i can maybe write this as a macro or something
so i can call it in the Expressions column in table loader rather than
having it hard coded there?

Thanks heaps!