topic deleting observations conditioned by previous observations in New SAS User

deleting observations conditioned by previous observations

RTN — Mon, 27 Jul 2020 11:38:11 GMT

I need help removing observations depending on previous observations.

I have

ID HA

1 1

1 3

2 3

3 2

3 3

4 1

4 2

4 3

I want

ID HA

1 1

2 3

3 2

4 1

For each ID I want to keep the observation where HA=1 or HA=2. If one ID has both HA=1 and HA=2, I only want to keep the one where HA=1. If an ID has neither HA=1 nor 2 (ie. only HA=3), then I want to keep all the observations with HA=3.

Any suggestions?

Re: deleting observations conditioned by previous observations

PaigeMiller — Mon, 27 Jul 2020 11:59:40 GMT

@RTN wrote:

For each ID I want to keep the observation where HA=1 or HA=2. If one ID has both HA=1 and HA=2, I only want to keep the one where HA=1. If an ID has neither HA=1 nor 2 (ie. only HA=3), then I want to keep all the observations with HA=3.

What about the third case, where one ID has either HA=1 or HA=2, but not both?

Re: deleting observations conditioned by previous observations

RTN — Mon, 27 Jul 2020 12:06:17 GMT

When one ID has neither HA=1 nor HA=2, then I want to keep all observations.

Re: deleting observations conditioned by previous observations

novinosrin — Mon, 27 Jul 2020 12:16:55 GMT

Hi @RTN Perhaps, what genius @mkeintz calls this "Readymeals" solution might just suffice 🙂 . So here's the lazy one-


data have;
input ID   HA;
cards;
1     1

1     3

2     3

2     3

2     3

3     2

3     3

3     3

4     1

4     2

4     3
;
proc sql;
create table want as
select *
from have
group by id
having min(ha)=ha;
quit;

Re: deleting observations conditioned by previous observations

Quentin — Mon, 27 Jul 2020 12:18:42 GMT

Are you ok with sorting the data? Can I assume that the only possible values for HA are 1, 2, 3?

Re: deleting observations conditioned by previous observations

PaigeMiller — Mon, 27 Jul 2020 12:18:54 GMT

@RTN wrote:
When one ID has neither HA=1 nor HA=2, then I want to keep all observations.

Actually, that wasn't my question.

Re: deleting observations conditioned by previous observations

RTN — Mon, 27 Jul 2020 12:22:31 GMT

I am ok with sorting the data and the only values for HA are 1,2 or 3.

Re: deleting observations conditioned by previous observations

Quentin — Mon, 27 Jul 2020 12:26:09 GMT

You could do it by sorting, then using first.id to pick off the first record. If it's a 1 or 2, you output it. If it's a 3, that means all records for that ID are 3's, and you want to output them all.

So you could do it like:

data have ;
 input ID HA ;
 cards ;
1     1
1     3
2     3
2     3
2     3
3     2
3     3
3     3
4     1
4     2
4     3
;
run ;

proc sort data=have ;
  by id ha ;
run ;

data want ;
  set have ;
  by id ;    
  retain allthrees ;
  if first.id then do ;
    if HA IN (1,2) then output ;
    allthrees=(HA=3) ;
  end ;
  if allthrees then output ;
run ;

Re: deleting observations conditioned by previous observations

Ksharp — Mon, 27 Jul 2020 12:47:28 GMT

If understood your question correctly .

data have ;
 input ID HA ;
 cards ;
1     1
1     3
2     3
2     3
2     3
3     2
3     3
3     3
4     1
4     2
4     3
;
run ;
data want;
 set have;
 by id ha;
 if first.id then n=0;
 n+first.ha;
 if n=1;
 drop n;
run;