topic create an counter conditionally in SAS Programming

create an counter conditionally

zhuxiaoyan1 — Wed, 12 Oct 2016 20:38:06 GMT

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.

Re: create an counter conditionally

ChrisNZ — Wed, 12 Oct 2016 20:56:10 GMT

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

Re: create an counter conditionally

Astounding — Wed, 12 Oct 2016 21:27:49 GMT

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

Re: create an counter conditionally

ChrisNZ — Wed, 12 Oct 2016 21:35:37 GMT

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;

Re: create an counter conditionally

zhuxiaoyan1 — Tue, 18 Oct 2016 12:58:32 GMT

Re: create an counter conditionally

zhuxiaoyan1 — Tue, 18 Oct 2016 13:00:16 GMT

Thanks, ChrisNZ! The idea works

Re: create an counter conditionally

zhuxiaoyan1 — Tue, 18 Oct 2016 13:01:18 GMT

Thanks, Astounding! this works.

Re: create an counter conditionally

zhuxiaoyan1 — Tue, 18 Oct 2016 13:02:19 GMT

Thanks a lot, ChrisNZ!