My data contains multiple observations per patient and shows as that some of them readmitted after death (death_status=1) due to clerical errors. I'd like to extract those cases to data "want".
data have;
input patid death_status hosp_adm_year logical $;
datalines;
1 0 2000 yes
1 0 2001 yes
1 1 2002 yes
2 1 1995 no
2 0 1996 no
3 0 2000 no
3 1 2002 no
3 0 2003 no
4 1 1990 no
4 0 1992 no
4 1 1995 no
4 0 2000 no
5 0 1991 yes
5 0 1992 yes
5 0 1993 yes
;
run;
data want;
input patid death_status hosp_adm_year logical $;
datalines;
2 1 1995 no
2 0 1996 no
3 0 2000 no
3 1 2002 no
3 0 2003 no
4 1 1990 no
4 0 1992 no
4 1 1995 no
4 0 2000 no
;
run;
Code below didn't work. Can I solve this problem in data steps somthing like this?
data test; set have;
by patid;
if first.patid=0 or last.patid=0 then output;
where death_status=1; run;
Assuming that records are sorted by patid and dates
data want;
do until(last.patid);
set have; by patid;
if death_status < max_death_status then clerical_error = 1;
max_death_status = max(max_death_status, death_status);
end;
do until(last.patid);
set have; by patid;
if clerical_error then output;
end;
drop max_death_status clerical_error;
run;
Assuming that records are sorted by patid and dates
data want;
do until(last.patid);
set have; by patid;
if death_status < max_death_status then clerical_error = 1;
max_death_status = max(max_death_status, death_status);
end;
do until(last.patid);
set have; by patid;
if clerical_error then output;
end;
drop max_death_status clerical_error;
run;
Your code would not work, if one id 's death_status are all 1.
data have;
infile cards expandtabs ;
input patid death_status hosp_adm_year logical $;
datalines;
1 0 2000 yes
1 0 2001 yes
1 1 2002 yes
2 1 1995 no
2 0 1996 no
3 0 2000 no
3 1 2002 no
3 0 2003 no
4 1 1990 no
4 0 1992 no
4 1 1995 no
4 0 2000 no
5 0 1991 yes
5 0 1992 yes
5 0 1993 yes
6 1 1991 yes
6 1 1992 yes
6 1 1993 yes
;
run;
data want;
n=0;
do until(last.patid);
set have; by patid;
if death_status=1 then n+1;
if death_status=1 and last.patid then last=1;
end;
do until(last.patid);
set have; by patid;
if n gt 1 or (n=1 and not last) then output;
end;
drop n;
run;
Yeah, OP wanted to detect resurrections; you are going one step further, detecting multiple deaths
@Ksharp, Thanks much. Detecting multiple death cases (1 all the way through) increased my end in the final output from 449 to 1372. I'm amazed. It shows that my deduplication efforts using probabilistic record linkage method is not perfect. All these 1372 needs a manual review now. I also like that indicator column created pointing to those specific cases.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.