DATA Step, Macro, Functions and more

How to read next record for consecutive rows of data.

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

How to read next record for consecutive rows of data.

[ Edited ]
  • 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.

 

 

TicketIdAssignedToAssignedDTMStatusResolutionTypeUpdatedByUpdatedDTMTATDate   
1209278214Jun2016 10:37:00.000ClosedoneFull & Final Settlement78214Jun2016 11:46:49.000 14Jun2016 0:00:00.000   
1209278214Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement77315Jun2016 11:15:29.060 14Jun2016 0:00:00.000   
1209278214Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement77315Jun2016 12:00:32.533 14Jun2016 0:00:00.000   
1209278214Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement78217Jun2016 12:52:44.820 14Jun2016 0:00:00.000   
1209478418Jun2016 10:37:00.000ClosedoneFull & Final Settlement78218Jun2016 11:46:49.000 18Jun2016 0:00:00.000   
1209478418Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement78218Jun2016 11:46:49.000 18Jun2016 0:00:00.000   
1209478418Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement78218Jun2016 11:46:49.000 18Jun2016 0:00:00.000   
1209478418Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement78218Jun2016 11:46:49.000 18Jun2016 0:00:00.000   

 

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


Accepted Solutions
Solution
‎07-31-2017 03:29 AM
Super User
Posts: 7,832

Re: BASE SAS

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Contributor
Posts: 22

BASE SAS

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.

TicketIdAssignedToAssignedDTMStatusResolutionTypeUpdatedByUpdatedDTMTATDate   
1209278214Jun2016 10:37:00.000ClosedoneFull & Final Settlement78214Jun2016 11:46:49.000 14Jun2016 0:00:00.000   
1209278214Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement77315Jun2016 11:15:29.060 14Jun2016 0:00:00.000   
1209278214Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement77315Jun2016 12:00:32.533 14Jun2016 0:00:00.000   
1209278214Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement78217Jun2016 12:52:44.820 14Jun2016 0:00:00.000   
1209478418Jun2016 10:37:00.000ClosedoneFull & Final Settlement78218Jun2016 11:46:49.000 18Jun2016 0:00:00.000   
1209478418Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement78218Jun2016 11:46:49.000 18Jun2016 0:00:00.000   
1209478418Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement78218Jun2016 11:46:49.000 18Jun2016 0:00:00.000   
1209478418Jun2016 10:37:00.000Re-OpendoneFull & Final Settlement78218Jun2016 11:46:49.000 18Jun2016 0:00:00.000   
Super User
Posts: 7,832

Re: BASE SAS

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 22

Re: BASE SAS

Posted in reply to KurtBremser

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. 

Solution
‎07-31-2017 03:29 AM
Super User
Posts: 7,832

Re: BASE SAS

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 22

Re: BASE SAS

Posted in reply to KurtBremser

Thank you @KurtBremser 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.

Super Contributor
Posts: 345

Re: SAS EG CODE HELP

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

Super User
Posts: 7,832

Re: Find change in status

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 177 views
  • 1 like
  • 3 in conversation