topic Re: how to output subgroups of observations based on the other variable containing a certain value? in SAS Programming

how to output subgroups of observations based on the other variable containing a certain value?

Cruise — Sun, 23 Apr 2017 01:49:13 GMT

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;

Re: how to output subgroups of observations based on the other variable containing a certain value?

PGStats — Sun, 23 Apr 2017 02:10:47 GMT

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;

Re: how to output subgroups of observations based on the other variable containing a certain value?

Cruise — Sun, 23 Apr 2017 02:30:28 GMT

Elegant it is. Gotta learn this multiple SET statement programming. Thanks much!

Re: how to output subgroups of observations based on the other variable containing a certain value?

Ksharp — Sun, 23 Apr 2017 03:31:25 GMT

@PGStats

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;

Re: how to output subgroups of observations based on the other variable containing a certain value?

PGStats — Sun, 23 Apr 2017 04:32:00 GMT

Yeah, OP wanted to detect resurrections; you are going one step further, detecting multiple deaths

Re: how to output subgroups of observations based on the other variable containing a certain value?

Cruise — Sun, 23 Apr 2017 13:50:06 GMT

@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.