Update current record with next record value (1 Viewer)

[Ben_Entrew]

Hi all,

I want to identify the time frame of a agent's job.
For example Agent 081 had a task aaa from 202012-202104, then it switched to task vvv from 202104 until now 202102.

I found this code:

data want;
recno=_n_+1;
set Task_times end=last;
if not last


then set Task_times (keep=TIME_ID rename=(TIME_ID=TO)) point=recno;
else call missing(TO);
run;


The output looks like:

TIME_ID	Agent	A_type	FROM	TO
202012​	081	aaa	202012​	202104​
202104​	081	vvv	202104​	202012​
202012​	082	aaa	202012​	202102​
202102​	082	vvv	202102​	202102​
202102​	083	aaa	202102​	202112​
202112​	083	vvv	202112​	202012​

It's not exactly what I want, I need to check also the Agents.

Does anyone know how I can add an additional condition checking wheter the Agent of the next row is the same or not?

Thank you.

Many greetings,
Ben

 

[arnelgp]

See Query1.

 

[Ben_Entrew]

Many Thanks Arnelgp,

forgot to mention I'm trying this in SAS.
Somehow SELECT TOP 1 doesn't work out here.

Any clue how to adjust this?

Thanks.

 

[arnelgp]

sorry, no idea.

 

[Pat Hartman]

In a relational database, no record is dependent on any other record but you are trying to make that happen. If this data is in YOUR database, then the correct solution is to "close" the open record when a new task is started. That solves the problem and makes the rows independent. If this is someone else's data, then you need a unique identifier on which to sort the rows and the previous ID must be the record you want to close if you can't use the method suggested by arnel.