How to read next record for consecutive rows of data.

[anirudhs]

• Hi Looking for the code to create a data set for only those rows who are having re-open status and are comming next after the closed once.
• Following is the data 
• data to be created of rows highlighted in red.

TicketId

AssignedTo

AssignedDTM

Status

Resolution

Type

UpdatedBy

UpdatedDTM

TAT

Date

12092

782

14Jun2016 10:37:00.000

Close

done

Full & Final Settlement

782

14Jun2016 11:46:49.000

14Jun2016 0:00:00.000

12092

782

14Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

773

15Jun2016 11:15:29.060

14Jun2016 0:00:00.000

12092

782

14Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

773

15Jun2016 12:00:32.533

14Jun2016 0:00:00.000

12092

782

14Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

782

17Jun2016 12:52:44.820

14Jun2016 0:00:00.000

12094

784

18Jun2016 10:37:00.000

Close

done

Full & Final Settlement

782

18Jun2016 11:46:49.000

18Jun2016 0:00:00.000

12094

784

18Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

782

18Jun2016 11:46:49.000

18Jun2016 0:00:00.000

12094

784

18Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

782

18Jun2016 11:46:49.000

18Jun2016 0:00:00.000

12094

784

18Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

782

18Jun2016 11:46:49.000

18Jun2016 0:00:00.000

As whose status = reopen and are having their previous status as closed.

[anirudhs]

Hi looking for the following help in code to create data from two consecutive rows where have to take only those rows which are occuring after CLOSE and are REOPEN.

As whose status = reopen and are having their previous status as closed.

Following is the data:

The new data set should contain only the highlighted data. which is coming next after close one. as seen in the dataset below.

TicketId

AssignedTo

AssignedDTM

Status

Resolution

Type

UpdatedBy

UpdatedDTM

TAT

Date

12092

782

14Jun2016 10:37:00.000

Close

done

Full & Final Settlement

782

14Jun2016 11:46:49.000

14Jun2016 0:00:00.000

12092

782

14Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

773

15Jun2016 11:15:29.060

14Jun2016 0:00:00.000

12092

782

14Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

773

15Jun2016 12:00:32.533

14Jun2016 0:00:00.000

12092

782

14Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

782

17Jun2016 12:52:44.820

14Jun2016 0:00:00.000

12094

784

18Jun2016 10:37:00.000

Close

done

Full & Final Settlement

782

18Jun2016 11:46:49.000

18Jun2016 0:00:00.000

12094

784

18Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

782

18Jun2016 11:46:49.000

18Jun2016 0:00:00.000

12094

784

18Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

782

18Jun2016 11:46:49.000

18Jun2016 0:00:00.000

12094

784

18Jun2016 10:37:00.000

Re-Open

done

Full & Final Settlement

782

18Jun2016 11:46:49.000

18Jun2016 0:00:00.000

[Kurt_Bremser]

Please refrain from double-posting, as I had to merge that post into here. And don't use non-descriptive subject lines.

[anirudhs]

hellow sir the solution you provided gives only one record , while i have to capture all the status in dataset which are occuring after CLOSE. 

eg. ticket 123 is assigne to person A and its closed and the same ticket is been assigned to B and its status is changed to re-open. if person B edits any change in ticket it gets saved. lin log dataset the multiple times person B clicks save button.

so the dataset is the log file. dtat maintained.so have to capture only those rows data when the tickets previous status was close and then was re-open.

As this data is been fetch from SQL and have column name as Status. 

[Kurt_Bremser · Accepted solution]

My code works.

Proof:

data have;
infile cards dlm='09'x dsd;
input
  ticketid
  assignedto
  assigneddtm :datetime22.3
  status $
  resolution $
  type :$25.
  updated_by
  updateddtm :datetime23.3
  TAT $
  date:datetime22.3
;
format
  assigneddtm
	updateddtm
	date
	  datetime22.3
;
cards;
12092	782	14Jun2016 10:37:00.000	Close	done	Full & Final Settlement	782	14Jun2016 11:46:49.000		14Jun2016 0:00:00.000
12092	782	14Jun2016 10:37:00.000	Re-Open	done	Full & Final Settlement	773	15Jun2016 11:15:29.060		14Jun2016 0:00:00.000 	  	  	 
12092	782	14Jun2016 10:37:00.000	Re-Open	done	Full & Final Settlement	773	15Jun2016 12:00:32.533		14Jun2016 0:00:00.000 	  	  	 
12092	782	14Jun2016 10:37:00.000	Re-Open	done	Full & Final Settlement	782	17Jun2016 12:52:44.820		14Jun2016 0:00:00.000 	  	  	 
12094	784	18Jun2016 10:37:00.000	Close	done	Full & Final Settlement	782	18Jun2016 11:46:49.000		18Jun2016 0:00:00.000 	  	  	 
12094	784	18Jun2016 10:37:00.000	Re-Open	done	Full & Final Settlement	782	18Jun2016 11:46:49.000		18Jun2016 0:00:00.000 	  	  	 
12094	784	18Jun2016 10:37:00.000	Re-Open	done	Full & Final Settlement	782	18Jun2016 11:46:49.000		18Jun2016 0:00:00.000 	  	  	 
12094	784	18Jun2016 10:37:00.000	Re-Open	done	Full & Final Settlement	782	18Jun2016 11:46:49.000		18Jun2016 0:00:00.000
;
run;

data want;
set have;
if status = 'Re-Open' and lag(status) = 'Close';
run;

proc print data=want noobs;
run;

Result:

ticketid	assignedto	assigneddtm	status	resolution	type	updated_by	updateddtm	TAT	date
12092	782	14JUN2016:10:37:00.000	Re-Open	done	Full & Final Settlement	773	15JUN2016:11:15:29.060		14JUN2016:00:00:00.000
12094	784	18JUN2016:10:37:00.000	Re-Open	done	Full & Final Settlement	782	18JUN2016:11:46:49.000		18JUN2016:00:00:00.000

[anirudhs]

Thank you @Kurt_Bremser the use of lag() function perectly worked as when sorted the data set on the bases of ticket id and updated_dtm.

Thanks a lot 

Anirudh.

[andreas_lds]

Please post the data as datastep, not as table. An be so kind to edit the subject, so that it describes the problem to be solved.

Some keywords, hopefully pointing you in the right direction: set, first, retain, output

[Kurt_Bremser]

data want;
set have;
if status = 'Re-Open' and lag(status) = 'Close';
run;

Look up the lag() function in the documentation and "subsetting if".