topic Re: Flaging of the group when multiple variables meet a condition in SAS Programming

Flaging of the group when multiple variables meet a condition

Zakharkou — Wed, 12 Apr 2023 21:11:51 GMT

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!!!

Re: Flaging of the group when multiple variables meet a condition

Quentin — Wed, 12 Apr 2023 21:41:27 GMT

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 ;

Re: Flaging of the group when multiple variables meet a condition

s_lassen — Thu, 13 Apr 2023 09:48:46 GMT

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;

Re: Flaging of the group when multiple variables meet a condition

s_lassen — Thu, 13 Apr 2023 09:53:18 GMT

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.

Re: Flaging of the group when multiple variables meet a condition

Kurt_Bremser — Thu, 13 Apr 2023 09:57:48 GMT

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 ;

Re: Flaging of the group when multiple variables meet a condition

Quentin — Thu, 13 Apr 2023 10:00:29 GMT

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

Re: Flaging of the group when multiple variables meet a condition

s_lassen — Thu, 13 Apr 2023 10:46:13 GMT

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 ;

Re: Flaging of the group when multiple variables meet a condition

Zakharkou — Thu, 13 Apr 2023 21:33:53 GMT

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.