DATA Step, Macro, Functions and more

create an counter conditionally

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

create an counter conditionally

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.


Accepted Solutions
Solution
‎10-18-2016 09:01 AM
PROC Star
Posts: 1,760

Re: create an counter conditionally

Posted in reply to Astounding

Indeed!  Smiley Embarassed

 

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;

View solution in original post


All Replies
PROC Star
Posts: 1,760

Re: create an counter conditionally

Posted in reply to zhuxiaoyan1

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
Super User
Posts: 5,513

Re: create an counter conditionally

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

Solution
‎10-18-2016 09:01 AM
PROC Star
Posts: 1,760

Re: create an counter conditionally

Posted in reply to Astounding

Indeed!  Smiley Embarassed

 

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;
Contributor
Posts: 65

Re: create an counter conditionally

Thanks a lot, ChrisNZ!
Contributor
Posts: 65

Re: create an counter conditionally

Posted in reply to Astounding
Thanks, Astounding! this works.
Contributor
Posts: 65

Re: create an counter conditionally

 
Contributor
Posts: 65

Re: create an counter conditionally

Thanks, ChrisNZ! The idea works
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 397 views
  • 0 likes
  • 3 in conversation