Flaging of the group when multiple variables meet a condition

[Zakharkou]

Dear SAS Community,

I have the following data set:

id var1 var2
1 1 0
1 0 1
1 1 0
1 1 0
2 0 1
2 0 1

How can I flag (for example flag=1) the whole group (ID) if var1 and var2 are equal to 1 at least once?

many thanks in advance!!!

[Quentin · Accepted solution]

First thought for DATA step was DOW loop, but SQL could do it too:

data have ;
  input id var1 var2 ;
  cards ;
1 1 0
1 0 1
1 1 0
1 1 0
2 0 1
2 0 1
3 1 0
4 1 1
;
run ;

proc sql ;
  select *
        ,(max(var1)=1 and max(var2)=1) as flag
  from have
  group by id
 ;
quit ;

[s_lassen]

Your SQL solution will only work if 1 is always the maximum value - if there are larger values as well, e.g. 2 or 888, your solution will not find the ones.

[Quentin]

Agree, I’m assuming that the OP is deriving a flag from other flags.

[Kurt_Bremser]

You might want to slightly change the code to meet @s_lassen 's concern:

proc sql ;
  select *
        ,(max(var1 > 0) and max(var2 > 0) as flag
  from have
  group by id
 ;
quit ;

[s_lassen]

Actually, @Zakharkou wanted to find out if there was a 1 in the group, not any positive value, so the SQL solution should be written as 

proc sql ;
  select *
        ,(max(var1 = 1) and max(var2 = 1) as flag
  from have
  group by id
 ;
quit ;

[s_lassen]

You can do it with a datastep, like this:

data want;                                                                                                                              
  do until(last.id);                                                                                                                    
    set have;                                                                                                                           
    by id;                                                                                                                              
    if var1=1 then flag1=1;                                                                                                             
    if var2=1 then flag2=1;                                                                                                             
    end;                                                                                                                                
  flag=flag1 and flag2;                                                                                                                 
  do until(last.id);                                                                                                                    
    set have;                                                                                                                           
    by id;                                                                                                                              
    output;                                                                                                                             
    end;                                                                                                                                
  drop flag1 flag2;                                                                                                                     
run;

[Zakharkou]

Thank you for the numerous solutions. Those 2 variables were indeed flags, so Quentin's solution is the easiest. The solution about the data step is also very interesting. Thanks again to all involved.