topic Re: calculating a time difference in SAS Enterprise Guide

calculating a time difference

Hans_Mahler — Tue, 17 Jul 2018 18:46:52 GMT

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

Re: calculating a time difference

PaigeMiller — Tue, 17 Jul 2018 18:50:53 GMT

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

Re: calculating a time difference

Reeza — Tue, 17 Jul 2018 19:05:05 GMT

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

Re: calculating a time difference

Hans_Mahler — Tue, 17 Jul 2018 19:12:19 GMT

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

Re: calculating a time difference

Reeza — Tue, 17 Jul 2018 19:22:41 GMT

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