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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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