BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
anirudhs
Obsidian | Level 7
  • 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

7 REPLIES 7
anirudhs
Obsidian | Level 7

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   
anirudhs
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Jade | Level 19

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 2842 views
  • 1 like
  • 3 in conversation