sql - 如何根据条件删除某些组中的某些观察值

Question

我想根据条件删除某些组中的一些观察结果。我的条件是每个组：如果 (imput="toto") 然后删除但如果整个组有 imput="toto" 然后保留它

data temp;
input siren $ class $ imput $;
cards;
A CP titi
B CP toto
C CE tata
D CE tata
F CM toto
G CM toto
H SU tata
I SU toto
;
run;

我想要的输出：

siren class imput
A CP titi
C CE tata
D CE tata
F CM toto
G CM toto
H SU tata

提前谢谢了 ！

标签： sqlsas

Answer 1

So you want to do two checks. Is the value toto and is the value only toto. Here is SQL that will do that. I had it explicitly create the checks as new variables so you can see what is happening. To see the check values for all observations remove the HAVING clause. If you are happy with it you can remove the check variables and just move the conditions into the having clause.

data temp;
  row+1;
  input siren $ class $ imput $;
cards;
A CP titi
B CP toto
C CE tata
D CE tata
F CM toto
G CM toto
H SU tata
I SU toto
;

proc sql ;
create table want as 
  select *
       , imput ne 'toto' as check1
       , max(imput ne 'toto') as check2
  from temp
  group by class 
  having check1 or not check2
  order by row
;
quit;

To do this with just a DATA step you will want add a "double DOW Loop" so you can calculate the overall flag for a group in the first loop and then process the individual rows in the second loop. Note this requires the input dataset is sorted (or at least grouped) by class.

data want;
  do until(last.class);
    set temp;
    by class notsorted;
    if imput ne 'toto' then check2=1;
  end;
  do until(last.class);
    set temp;
    by class notsorted;
    if imput ne 'toto' or not check2 then output;
  end;
  drop check2;
run;