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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.