Desktop productivity for business analysts and programmers

calculating a time difference

Reply
New Contributor
Posts: 2

calculating a time difference

Hey Guys!

sorry for my english but I hope you  understand and could help me.


I want to get the following information:

 

The time betweeen the timestamps "e"  in seconds from one object with an unique "ipid".

Please see my attachement.

 

I tried with DATADIFF and LAG:


SELECT DATEDIFF (second, ptime, e), q.*
FROM (SELECT *,
LAG(e) OVER (PARTITION BY ipid ORDER BY e) ptime
FROM Table ) q
WHERE ptime IS NOT NULL

But: "The LAG function is not supported in PROC SQL, it is only valid within the DATA step."

 

You have any other idea?

 

Thanks!!!

Respected Advisor
Posts: 3,258

Re: calculating a time difference

Posted in reply to Hans_Mahler

You could do the calculations in a data step instead of PROC SQL. Then the LAG function would work.

 

 

--
Paige Miller
Super User
Posts: 23,980

Re: calculating a time difference

Posted in reply to Hans_Mahler

OVER BY and PARTITION are also not supported so your best best is to switch to a data step.

You can also use the DIF() function directly instead.

 


@Hans_Mahler wrote:

Hey Guys!

sorry for my english but I hope you  understand and could help me.


I want to get the following information:

 

The time betweeen the timestamps "e"  in seconds from one object with an unique "ipid".

Please see my attachement.

 

I tried with DATADIFF and LAG:


SELECT DATEDIFF (second, ptime, e), q.*
FROM (SELECT *,
LAG(e) OVER (PARTITION BY ipid ORDER BY e) ptime
FROM Table ) q
WHERE ptime IS NOT NULL

But: "The LAG function is not supported in PROC SQL, it is only valid within the DATA step."

 

You have any other idea?

 

Thanks!!!


 

New Contributor
Posts: 2

Re: calculating a time difference

Posted in reply to Hans_Mahler
sorry but i dont know how this works in DATA step? you mean i sort my data and then..?
Super User
Posts: 23,980

Re: calculating a time difference

Posted in reply to Hans_Mahler
*sort data for by group processing;
proc sort data=sashelp.stocks out=stocks1; by stock date;

data stocks2;
set stocks1;
by stock date;

*get previous value;
prev_open = lag(open);
*if first record for the stock group, set to missing to avoid using 
previous stocks value;
if first.stock then call missing(prev_open);

*calculate difference when not missing;
if not missing(prev_open) then difference = open - prev_open;
run;
Ask a Question
Discussion stats
  • 4 replies
  • 71 views
  • 0 likes
  • 3 in conversation