BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zhuxiaoyan1
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

7 REPLIES 7
ChrisNZ
Tourmaline | Level 20

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
PROC Star

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
Tourmaline | Level 20

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;
zhuxiaoyan1
Quartz | Level 8
Thanks a lot, ChrisNZ!
zhuxiaoyan1
Quartz | Level 8
Thanks, Astounding! this works.
zhuxiaoyan1
Quartz | Level 8
 
zhuxiaoyan1
Quartz | Level 8
Thanks, ChrisNZ! The idea works

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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