how do I lag continuous dates by a specific variable?

[amandav2107]

Hello,

I have a listing of patients and their dates of admits and releases by facility type. 

For example, patient 1 has four lines. He was in facility A from 09OCT23 - 22DEC23 then facility B continuously from 23JUL24 - . (current). 

I would like in my final table to have only 2 lines for patient 1: admit=09OCT23 - release=22DEC23 and admit=30AUG24 - release=. 

I only care about the admits for facility A, BUT if he was in facility A and was transferred to facility B in a continous time series, I want the last release date in the series, regadless of the facility. In this scenario, he started on 23JUL24 in facility B and ended in facility A but I only want the facility A date range admit=30AUG24 and release=.

Please help, I tried with retain, with LAG... but without success... Thank you.

my initial table:

id	facility	amit	release
1	A	09OCT23	22DEC23
1	B	23JUL24	27AUG24
1	B	27AUG24	30AUG24
1	A	30AUG24	.
2	A	15DEC23	14JAN24
2	B	14JAN24	22JAN24
2	A	22JAN24	08MAR24

and my final table would be:

id	facility	admit	release
1	A	09OCT23	22DEC23
1	A	30AUG24	.
2	A	15DEC23	08MAR24

Here's what I tried:

proc sort data=DELETE; by id admit;
data TEST (drop=_:);
do until ((release+1 < _next_admit) or last.juvnum=1);
set DELETE;
by id admit;
merge DELETE DELETE (firstobs=2 keep=admit rename=(admit=_next_admit));
_min_admit=min(_min_admit,admit);
if release=. and last.admit=0 then release=_next_admit-1;
end;
admit=_min_admit;
run;

But, with this code, I get this. The first record is okay, but the second line starts with the admit of facility B and I only want facility A admits and then the release date is that of the next id admit. 

id	facility	admit	release
1	A	09OCT23	22DEC23
1	A	23JUL24	15DEC23
2	A	15DEC23	08MAR24

Thank you in advance!

[mkeintz]

Sample data please, in a working DATA step.  Help us help you.

[mkeintz]

You need a way to look ahead at the next observation, to compare its ADMIT date with the current RELEASE.  If they don't overlap, then you can output:

data have;
  input id	facility :$1.	admit :date9.	release :date9.;
  format admit release date9.;
datalines;
1	A	09OCT23	22DEC23
1	B	23JUL24	27AUG24
1	B	27AUG24	30AUG24
1	A	30AUG24	.
2	A	15DEC23	14JAN24
2	B	14JAN24	22JAN24
2	A	22JAN24	08MAR24
run;

data want (drop=_: nxt_: );
  do until (last.id or release < nxt_admit);
    set have ;
    by id;
    merge have 
          have (firstobs=2 keep=admit rename=(admit=nxt_admit));
    if facility='A' then _initial_admit=coalesce(_initial_admit,admit);
  end;
  if _initial_admit^=.;
  admit=_initial_admit;
run;

[Ksharp]

There are too many scenarios you need to consider about.

Assuming I understood what you mean.

data have;
infile cards expandtabs ;
input id	facility $	(amit	release) (:date9.);
format amit	release date9.;
cards;
1	A	09OCT23	22DEC23
1	B	23JUL24	27AUG24
1	B	27AUG24	30AUG24
1	A	30AUG24	.
2	A	15DEC23	14JAN24
2	B	14JAN24	22JAN24
2	A	22JAN24	08MAR24
;

data temp;
  set have;
  by id;
  if first.id or amit>lag(release)+1 then group+1;
run;
data want;
 set temp;
 by group;
 retain temp found;
 if first.group then call missing(temp,found);
 if facility='A' and not found then do;temp=amit;found=1;end;

 if last.group then do;
   amit=coalesce(temp,amit);
   output;
 end;
 drop group temp found;
run;

[Ksharp]

For the sake of the robust/strong of code, I would like to use this code:

data have;
infile cards expandtabs ;
input id	facility $	(amit	release) (:date9.);
format amit	release date9.;
cards;
1	A	09OCT23	22DEC23
1	B	23JUL24	27AUG24
1	B	27AUG24	30AUG24
1	A	30AUG24	.
2	A	15DEC23	14JAN24
2	B	14JAN24	22JAN24
2	A	22JAN24	08MAR24
;

data temp;
  set have;
  do date=amit to coalesce(release,date());
   output;
  end;
  keep id facility date;
run;
proc sort data=temp;by id date;run;
data temp2;
 set temp;
 by id;
 if first.id or dif(date)>1 then group+1;
run;
proc sql;
create table want as
 select group,id,facility,min(date) as amit	format=date9.,ifn(max(date)=date(),.,max(date)) as release format=date9.
  from temp2
   where facility='A'
    group by group,id,facility;
quit;