calculating a time difference

[Hans_Mahler]

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!!!

[PaigeMiller]

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

[Reeza]

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!!!

---

[Hans_Mahler]

sorry but i dont know how this works in DATA step? you mean i sort my data and then..?

[Reeza]

*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;