BookmarkSubscribeRSS Feed
_LB
Fluorite | Level 6 _LB
Fluorite | Level 6
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
8 REPLIES 8
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Fluorite | Level 6 _LB
Fluorite | Level 6
Thanks SPR-
Close, but still at the same place I was before..
Ugh!

Lawrence
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Super User
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
Fluorite | Level 6
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
Calcite | Level 5
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
Fluorite | Level 6 _LB
Fluorite | Level 6
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
Fluorite | Level 6 _LB
Fluorite | Level 6
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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 8 replies
  • 1153 views
  • 0 likes
  • 5 in conversation