Accepted Solutions
You can create a 'synthetic' group based on visit date differences, and perform your counting based on the groups that achieve 4 or more members. (1 for first admit, plus at least presumed 3 readmits)
Because you are processing date ordered data in the two different groupings
- BY hosp id
- BY id
you will want to process each one separately.
Data
data have;
length Hosp $1 ID $3 ;
input Hosp ID Date mmddyy8.;
format Date mmddyy10.;
datalines;
A 999 01/01/19
A 123 01/01/19
A 123 01/05/19
A 123 02/03/19
A 123 02/10/19
A 123 05/12/19
A 123 12/05/19
B 999 01/15/19
B 456 04/03/18
B 456 05/05/18
B 123 01/02/18
B 123 05/06/19
B 123 06/07/19
B 123 07/07/19
C 999 01/21/19
C 678 01/01/18
C 678 04/06/18
C 678 06/08/18
C 678 07/09/18
D 123 01/01/18
D 123 05/03/18
E 999 02/15/19
E 123 01/03/18
E 123 05/07/19
run;
Synthetic group computation. From a first date check if the date is within 1 year continuous. If so, the date is in the group and gets a new within group sequence number. The group is 'reset' at the start of a by group or when the gap between visits is at least a year.
BY HOSP and ID
proc sort data=have;
by hosp id date;
data want_by_hospital_readmits;
set have;
by hosp id;
retain date1 0;
if first.id OR intck('year', date1, date, 'C') > 0 then do;
group+1;
seq=1;
date1 = date;
end;
else
seq+1;
format date1 mmddyy10.;
run;
proc sql;
create table howmany1 as
select count(distinct id) as howmany from want_by_hospital_readmits
where seq=4;
create table whichmany1 as
select distinct(id) from want_by_hospital_readmits
where seq=4;
BY only ID
proc sort data=have;
by id date;
run;
data want_by_readmits;
set have;
by id;
retain date1 0;
if first.id OR intck('year', date1, date, 'C') > 0 then do;
group+1;
seq=1;
date1 = date;
end;
else
seq+1;
format date1 mmddyy10.;
run;proc sql;
create table howmany2 as
select count(distinct id) as howmany from want_by_readmits
where seq=4;
create table whichmany2 as
select distinct(id) from want_by_readmits
where seq=4;
There can be some confounding sequences of dates where a legit first date might be embedded in a group that 'failed' to reach 3.
You can create a 'synthetic' group based on visit date differences, and perform your counting based on the groups that achieve 4 or more members. (1 for first admit, plus at least presumed 3 readmits)
Because you are processing date ordered data in the two different groupings
- BY hosp id
- BY id
you will want to process each one separately.
Data
data have;
length Hosp $1 ID $3 ;
input Hosp ID Date mmddyy8.;
format Date mmddyy10.;
datalines;
A 999 01/01/19
A 123 01/01/19
A 123 01/05/19
A 123 02/03/19
A 123 02/10/19
A 123 05/12/19
A 123 12/05/19
B 999 01/15/19
B 456 04/03/18
B 456 05/05/18
B 123 01/02/18
B 123 05/06/19
B 123 06/07/19
B 123 07/07/19
C 999 01/21/19
C 678 01/01/18
C 678 04/06/18
C 678 06/08/18
C 678 07/09/18
D 123 01/01/18
D 123 05/03/18
E 999 02/15/19
E 123 01/03/18
E 123 05/07/19
run;
Synthetic group computation. From a first date check if the date is within 1 year continuous. If so, the date is in the group and gets a new within group sequence number. The group is 'reset' at the start of a by group or when the gap between visits is at least a year.
BY HOSP and ID
proc sort data=have;
by hosp id date;
data want_by_hospital_readmits;
set have;
by hosp id;
retain date1 0;
if first.id OR intck('year', date1, date, 'C') > 0 then do;
group+1;
seq=1;
date1 = date;
end;
else
seq+1;
format date1 mmddyy10.;
run;
proc sql;
create table howmany1 as
select count(distinct id) as howmany from want_by_hospital_readmits
where seq=4;
create table whichmany1 as
select distinct(id) from want_by_hospital_readmits
where seq=4;
BY only ID
proc sort data=have;
by id date;
run;
data want_by_readmits;
set have;
by id;
retain date1 0;
if first.id OR intck('year', date1, date, 'C') > 0 then do;
group+1;
seq=1;
date1 = date;
end;
else
seq+1;
format date1 mmddyy10.;
run;proc sql;
create table howmany2 as
select count(distinct id) as howmany from want_by_readmits
where seq=4;
create table whichmany2 as
select distinct(id) from want_by_readmits
where seq=4;
There can be some confounding sequences of dates where a legit first date might be embedded in a group that 'failed' to reach 3.
Thank you @RichardDeVen for a detailed explation, this helps.
One more question. I thought I got it, but I dont think I do.
So the condition of wanting to count from the initial visit I dont think is working.
For example, client 123 for Hosp B (row 9) we see that the initial visit was on 1/2/2018 and the visit on row 9 is on 5/6/2019, more than a year apart, so shouldnt the group and seq reset? Also shouldnt date1 then reset on row 9 to reflect 5/6/2019 and restart the seq from 1? That is what I am looking for.
Same for Row 22 Hosp E same client, 123.
Maybe the OR statement in "if first.id OR intck('year', date, date1, 'C') > 0 then do;" is not doing what we want it to do? should it be an and? or should there be two if statements?
Thank you soo much for helping and guiding me on this, I am learning alot! I appreciate your help, you all are awesome!
Thank you @RichardDeVen , I understand that we are processing two different ways so we would be sorting by two different ways.
If we were to just look at the first method of processing by hosp id date, with the condition of counting admits in a 12 month period by hosp for each id we see that there is step missing (and this is where I am puzzled as to what we need to do).
In the picture below we see that in row 9 the group should change to the next number (4) and the seq should reset to 1 because the visit for ID 123 in hosp B is more than 12 months. (same for row 23)
How can we reset the group and seq back to 1? Again, if we only address the first question at this point?
This is very helpful though and I truly appreciate your assistance.
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.
-
1564 views
-
0 likes
-
-
