topic Re: BASE SAS in SAS Programming

How to read next record for consecutive rows of data.

anirudhs — Fri, 28 Jul 2017 10:46:52 GMT

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

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

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.

BASE SAS

anirudhs — Fri, 28 Jul 2017 07:52:28 GMT

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

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

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

Re: SAS EG CODE HELP

andreas_lds — Fri, 28 Jul 2017 07:54:34 GMT

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

Re: Find change in status

Kurt_Bremser — Fri, 28 Jul 2017 07:58:28 GMT

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

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

Re: BASE SAS

Kurt_Bremser — Fri, 28 Jul 2017 08:04:06 GMT

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

Re: BASE SAS

anirudhs — Fri, 28 Jul 2017 09:40:15 GMT

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.

Re: BASE SAS

Kurt_Bremser — Fri, 28 Jul 2017 10:53:29 GMT

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

Re: BASE SAS

anirudhs — Mon, 31 Jul 2017 07:31:38 GMT

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.