SAS：根据两列和多行中的值选择观察组

In SAS, I need to select subjects and their data rows based on values in two variables across several rows. In the data below ID is the relevant BY group. I need to output the group of rows associated with a person who has X in (0,1,9) and Y=missing on all rows. Therefore no rows would be outputted for ID=01 because it has an X=1 and non-missing Y in two other rows. Two rows must be output for ID=02 and ID=03. And the row for ID=04 must be output. Thanks.

在SAS中,我需要根据跨越多行的两个变量中的值来选择主题及其数据行。在下面的数据中,ID是相关的BY组。我需要输出与在所有行中具有X(0,1,9)和Y =缺失的人相关联的行组。因此,对于ID = 01,不会输出任何行,因为它在另外两行中具有X = 1且不缺少Y.对于ID = 02和ID = 03,必须输出两行。并且必须输出ID = 04的行。谢谢。

ID  X Y  
01  1 .  
01  . 1  
01  . 1  
02  0 .  
02  . .  
03  9 .  
03  . .  
04  1 .  

2 个解决方案

#1

Try this:

data have;
input ID $ X Y;
cards;
01 1 .
01 . 1
01 . 1
02 0 .
02 . .
03 9 .
03 . .
04 1 .
;
proc sql;
  select * from have group by id having x in(0,1,9) and sum(y) is null;
quit;

#2

data have;
input ID $ X Y;
cards;
01 1 .
01 . 1
01 . 1
02 0 .
02 . .
03 9 .
03 . .
04 1 .
;
run;

proc sort data=have;
  by id;
run;

data list;
set have;
by id;
retain keepit;
if first.id then keepit = .;

if missing(keepit) or keepit=1 then do;
    if missing(y) then do;
        if x in (0,1,9) then keepit = 1;
    end;
    else keepit = 0;
end;

if last.id and keepit then output;
keep id;
run;

data want;
    merge
        have (in=a)
        list (in=b)
    ;
    by id;
    if a and b;
run;