BookmarkSubscribeRSS Feed
Hans_Mahler
Calcite | Level 5

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

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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

 

 

--
Paige Miller
Reeza
Super User

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
Calcite | Level 5
sorry but i dont know how this works in DATA step? you mean i sort my data and then..?
Reeza
Super User
*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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 623 views
  • 0 likes
  • 3 in conversation