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

Solved
Super Contributor
Posts: 383

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

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;``````

Accepted Solutions
Solution
‎04-22-2017 10:28 PM
Posts: 5,614

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

[ Edited ]

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;``` ```

PG

All Replies
Solution
‎04-22-2017 10:28 PM
Posts: 5,614

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

[ Edited ]

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;``` ```

PG
Super Contributor
Posts: 383

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

Elegant it is. Gotta learn this multiple SET statement programming. Thanks much!
Super User
Posts: 10,846

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

@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; ``````
Posts: 5,614

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

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

PG
Super Contributor
Posts: 383

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

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

☑ This topic is solved.