Best solution to combine room/bed information over period of time...

[_LB]

Hello all;
This is more of a strategic question and/or technical one as I have hit a wall on this.

In essence I have the following information for example:


Visitno Room Bed InDate
1234 1 1 JAN 1, 2011
1234 1 1 JAN 2, 2011
1234 5 2 JAN 3, 2011
1234 5 2 JAN 4, 2011
1234 5 2 JAN 5, 2011
...
1234 5 2 JAN 10, 2011
1234 1 1 JAN 11, 2011
1234 1 1 JAN 12, 2011

I am attempting to compress this information so that it reads:

Visitno Room Bed InDate LastDate
1234 1 1 JAN 1, 2011 JAN 2, 2011
1234 5 2 JAN 3, 2011 JAN 10, 2011
1234 1 1 JAN 11, 2011 JAN 12, 2011

I have a program that largely works 99% of the time, but in certain case scenarios (such as above) the same patient ends up back in the same bed in the same room on the same floor if the patient is here long enough and it leads to erroneous output so that it would read:
Visitno Room Bed InDate LastDate
1234 1 1 JAN 1, 2011 JAN 12, 2011
1234 5 2 JAN 3, 2011 JAN 10, 2011


Any guidance would be great!

Lawrence

[SPR]

Hello ~LB,

This is a solution:
[pre]
data r (rename=(sd=InDate ed=LastDate));
retain Visitno Room Bed sd ed;
set i;
if First.Visitno then sd=InDate;
LAGd=LAG(InDate);
if LAGd ne . and InDate-LAGd > 1 then do; ed=LAGd; output; sd=InDate; end;
if Last.Visitno then do; ed=InDate; output; end;
by Visitno;
format sd ed date7.;
drop InDate LAGd;
run;
[/pre]
Sincerely,
SPR

[_LB]

Thanks SPR-
Close, but still at the same place I was before..
Ugh!

Lawrence

[SPR]

Sorry, Lawrence, I did not realized that you need the following output:
Visitno Room Bed InDate LastDate
1234 1 1 JAN 1, 2011 JAN 2, 2011
1234 5 2 JAN 3, 2011 JAN 10, 2011
1234 1 1 JAN 11, 2011 JAN 12, 2011

rather then this:
Visitno Room Bed InDate LastDate
1234 1 1 JAN 1, 2011 JAN 2, 2011
1234 5 2 JAN 3, 2011 JAN 10, 2011
1234 1 1 JAN 11, 2011 JAN 12, 2011

SPR

[Ksharp]

Actually .You can make a flag variable to distinguish the obs have the same room and the same bed.try this:


[pre]
data temp;
input Visitno Room Bed InDate & $20.;
datalines;
1234 1 1 JAN 1, 2011
1234 1 1 JAN 2, 2011
1234 5 2 JAN 3, 2011
1234 5 2 JAN 4, 2011
1234 5 2 JAN 5, 2011
1234 5 2 JAN 10, 2011
1234 1 1 JAN 11, 2011
1234 1 1 JAN 12, 2011
;
run;
data temp;
set temp;
if (visitno ne lag(visitno)) or (room ne lag(room) and indate ne lag(indate))
then flag+1;
run;
data want;
set temp;
by flag;
retain _indate;
if first.flag then _indate=indate;
if last.flag then do;
lastdat=indate;
output;
end;
drop indate flag;
run;
[/pre]


Ksharp

[RickM]

If the data is already sorted the way you presented it then you could use the notsorted option in a by statement.

[pre]
data one;
input Visitno Room Bed InDate $ 10-21;
cards;
1234 1 1 JAN 1, 2011
1234 1 1 JAN 2, 2011
1234 5 2 JAN 3, 2011
1234 5 2 JAN 4, 2011
1234 5 2 JAN 5, 2011
1234 5 2 JAN 10, 2011
1234 1 1 JAN 11, 2011
1234 1 1 JAN 12, 2011
;;;
run;

data two;
set one;
retain indt outdt;
by room notsorted;
if first.room then indt=InDate;
else if last.room then do;
outdt=InDate;
output;
end;
run;
[/pre]

[AmarMundankar]

Hi Lawrence,
Try the following:

data temp;
input Visitno Room Bed InDate & $20.;
datalines;
1234 1 1 JAN 1, 2011
1234 1 1 JAN 2, 2011
1234 5 2 JAN 3, 2011
1234 5 2 JAN 4, 2011
1234 5 2 JAN 5, 2011
1234 5 2 JAN 10, 2011
1234 1 1 JAN 11, 2011
1234 1 1 JAN 12, 2011
;
data first last;
set temp;
by room bed indate notsorted;
if first.room = last.room then delete;
if first.room then output first;
if last.room then output last;
run;
data x;
merge first last(keep = indate rename=(indate = lastdate));
run;

Thanks,
Amar Mundankar.

[_LB]

Amar;
Your solution works very well! However I did run into one hiccup. I then had to add the unit to the variables and it works 98% of the time but it has the power to skip a unit,

below is actual data (with visit # changed). Your program worked perfectly even not including the unit but when it came to line 5 (see the x next to it) this unit/bed was omitted.

Also one last level of complexity-the visit no was not included. This will also be integral as there are multiple visit numbers. I should have included a better example.

Any additional info/assistance you can provide would be great!
Best,

Lawrence

VISITNO UNIT Room BED IN_DATE
17182355 Z5N B507 1 9/21/2010
17182355 Z5N B507 1 9/22/2010
17182355 Z5N B507 1 9/23/2010
17182355 Z5N B507 1 9/24/2010
17182355 Z4I A434 K 9/25/2010
17182355 Z5N B507 1 9/26/2010
17182355 Z5N B507 1 9/27/2010
17182355 Z5N B507 1 9/28/2010
17182355 Z5N B507 1 9/29/2010
17182355 Z5N B507 1 10/1/2010
17182355 Z5N B507 1 10/2/2010
17182355 Z5N B507 1 10/3/2010
17182355 Z5N B507 1 10/4/2010
17296714 Z5N B514 1 10/15/2010
17296714 Z5N B514 1 10/16/2010
17296714 Z5N B514 1 10/17/2010
17296714 Z5N B514 1 10/18/2010
17296714 Z5N B514 1 10/19/2010
17296714 Z5N B514 1 10/20/2010
17296714 Z5N B514 1 10/21/2010
17296714 Z5N B514 1 10/22/2010
17296714 Z5N B514 1 10/23/2010
17296714 Z5N B514 1 10/26/2010
17296714 Z5N B514 1 10/27/2010
17296714 Z5N B514 1 10/28/2010
17296714 Z5N B514 1 10/29/2010
17296714 Z5N B514 1 10/30/2010
17364770 Z5N B526 1 11/2/2010
17364770 Z5N B526 1 11/3/2010
17364770 Z5N B526 1 11/4/2010
17364770 Z5N B526 1 11/5/2010
17501920 Z4E 428 A 12/3/2010
17501920 Z4E 428 A 12/4/2010
17501920 Z5N B507 1 12/5/2010
17501920 Z5N B507 1 12/6/2010
17501920 Z5N B507 1 12/7/2010
17501920 Z5N B507 1 12/8/2010
17621708 Z5N B528 1 1/5/2011
17621708 Z5N B528 1 1/6/2011
17621708 Z5N B528 1 1/7/2011
17621708 Z5N B528 1 1/8/2011
17621708 Z5N B528 1 1/9/2011
17621708 Z5N B528 1 1/10/2011
17621708 Z5N B528 1 1/11/2011
17621708 Z5N B528 1 1/12/2011
17676853 Z4I A434 C 1/17/2011
17676853 Z4I A434 C 1/18/2011
17676853 Z5N B516 1 1/19/2011
17676853 Z5N B516 1 1/20/2011
17676853 Z5N B516 1 1/21/2011
17676853 Z5N B516 1 1/22/2011
17676853 Z5N B516 1 1/23/2011
17676853 Z5N B516 1 1/24/2011
17676853 Z5N B516 1 1/25/2011
17676853 Z5N B516 1 1/26/2011
17676853 Z5N B516 1 1/27/2011
17676853 Z5N B516 1 1/28/2011
17676853 Z5N B516 1 1/29/2011
17676853 Z5N B516 1 1/30/2011
17676853 Z5N B516 1 1/31/2011
17676853 Z4I A434 C 2/1/2011
17676853 Z4I A434 C 2/2/2011
17676853 Z5N B529 2 2/3/2011
17676853 Z5N B529 2 2/4/2011
17676853 Z5N B529 2 2/5/2011
17676853 Z5N B529 2 2/6/2011
17676853 Z5N B529 2 2/7/2011
17676853 Z5N B529 2 2/8/2011
17676853 Z5N B529 2 2/9/2011
17676853 Z5N B529 2 2/10/2011
17676853 Z5N B529 2 2/11/2011
17676853 Z5N B529 2 2/12/2011
17676853 Z5N B529 2 2/13/2011
17676853 Z5N B529 2 2/14/2011
17676853 Z5N B529 2 2/15/2011
17676853 Z5N B529 2 2/16/2011
17676853 Z5N B529 2 2/17/2011
17676853 Z5N B529 2 2/18/2011

[_LB]

Amar;
I figured it out. by removing the if first.room = last.room then delete statement I got the missing unit back..

Thanks again!

Lawrence