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".

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

Posts: 3,258

## Re: calculating a time difference

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

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".

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

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

``````*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;``````
Discussion stats
• 4 replies
• 71 views
• 0 likes
• 3 in conversation