create an counter conditionally

[zhuxiaoyan1]

I have this data:

ID   Start_DT      End_DT

a   01/01/2016   01/02/2016

a   01/02/2016   01/04/2016

b   02/03/2016   02/04/2016

b   02/05/2016   02/07/2016

c   04/05/2016   04/07/2016

I want to create a Admit_ID like this:

ID   Start_DT      End_DT        Admit_ID

a   01/01/2016   01/02/2016          1

a   01/02/2016   01/04/2016          1

b   02/03/2016   02/04/2016          2

b   02/05/2016   02/07/2016          2

c   04/05/2016   04/07/2016          3

if row one and row 2 are same and the End_DT in row one is the same as the Start_DT at row two, they get same Admit_ID or The End_DT of the row is one day apart from the start_DT of the next row like row 3 and row 4, the two rows still get same Admit_ID; otherwise they get different Admit_ID like row 5.

[ChrisNZ]

Like this?

data HAVE;

input ID $1. START_DT : mmddyy10. END_DT : mmddyy10.;

format START_DT END_DT date9.;

cards;

a 01/01/2016 01/02/2016

a 01/02/2016 01/04/2016

b 02/03/2016 02/04/2016

b 02/05/2016 02/07/2016

c 04/05/2016 04/07/2016

run;

data WANT;

set HAVE;

LAG_ID =lag(ID);

LAG_START_DT=lag(START_DT);

if ID ne LAG_ID | END_DT-LAG_START_DT in (0,1) then ADMIT_ID+1;

drop LAG:;

run;

proc print noobs; run;

ID	START_DT	END_DT	ADMIT_ID
a	01JAN2016	02JAN2016	1
a	02JAN2016	04JAN2016	1
b	03FEB2016	04FEB2016	2
b	05FEB2016	07FEB2016	2
c	05APR2016	07APR2016	3

[Astounding]

Chris, I think you have the right idea.  But I'm not sure about one piece.  Shouldn't the subtraction look at:

START_DT - LAG_END_DT

[ChrisNZ · Accepted solution]

Indeed! 

there are 2 errors that correct each other!  This is better:

data HAVE;
input ID $1. START_DT  : mmddyy10.  END_DT : mmddyy10.;
format  START_DT  END_DT date9.;
cards; 
a   01/01/2016   01/02/2016
a   01/02/2016   01/04/2016
b   02/03/2016   02/04/2016
b   02/05/2016   02/07/2016
c   04/05/2016   04/07/2016
run;

data WANT;
  set HAVE;
  PREV_ID     =lag(ID);
  PREV_END_DT =lag(END_DT);
  if  ID ne PREV_ID | START_DT-PREV_END_DT ^in (0,1) then ADMIT_ID+1;
  drop PREV:;
run;

proc print noobs; run;

[zhuxiaoyan1]

Thanks a lot, ChrisNZ!

[zhuxiaoyan1]

Thanks, Astounding! this works.

[zhuxiaoyan1]

Thanks, ChrisNZ! The idea works