Where Funktion with the selection of all the variables in a subgroup

[Zakharkou]

Hi dear community,

I need some help with the following function. I'm trying to set a flag (like var2=1) for all the lines where all the variables in the subgroup ProbImgId have the var1 Variable > 2. Here is the database sample.

ProbId      ProbImgId       var1

1               1                     1

1               2                     1

1               3                     0

2               1                     1

2               2                     2

3               1                     2

3               2                     2  

3               3                     2

3               4                     3

I look forward to your suggestions. Thanks a lot.

[Kurt_Bremser]

So you want to ser var2 = 1 only for ProbId= 3 and ProbImgId = 4?

Which value shall var2 get in the other cases?

[Zakharkou]

No, I want to see var2=1 for all the lines of the ProbId and ProbImgId where var1 > 2. So as shown in this case ProbId=3 and ProbImgId=1--4.
In other cases, can we set the Variable var2=0.

Thanks a lot.

[Tom]

If I am translating that properly you want to set a flag at the PROBID level (ie the flag has the same value for all observations with the same value of PROBID) that indicates whether ANY of the observations for that level of PRODID had VAR1 larger than 2.

proc sql;
create table want as
  select *
       , max( var1>2 ) as FLAG
  from have
  group by PRODID
;
quit;

SAS will evaluate a boolean expression, like (var1>2), as 1 for TRUE and 0 for FALSE.

SAS will automatically remerge the aggregate MAX() function result onto all observations for the group.

The maximum value will indicate if the expression was ever TRUE.  The only way the MAX() could be zero is if it was zero (FALSE) for all of the observations in the group.

[Zakharkou]

Almost everything right except one point - not any but all of the observations.

[Tom]

---

@Zakharkou wrote:
Almost everything right except one point - not any but all of the observations.

---

If the requirement is that ALL of the observations have VAR1>2 then NONE of the groups meet that criteria.

[Zakharkou]

You are right, but it is only an example. Let's make >1 then 😃

[Tom]

Figure it out.

Consider these combinations:

max( var1 > 1)
min( var1 > 1);
max( var1 <= 1);
min(  var1 <= 1);
max(var1) > 1
max(var1) <= 1
min(var1) > 1
min(var1) <= 1

[Tom · Accepted solution]

data have;
input ProbId ProbImgId var1;
cards;
1 1 1
1 2 1
1 3 0
2 1 1
2 2 2
3 1 2
3 2 2  
3 3 2
3 4 3
;

proc sql;
create table want as
  select *
       , max(var1) as max
       , min(var1) as min
       , max(var1) > 1 as any_gt1
       , min(var1) > 1 as all_gt1
  from have
  group by probid
;

proc print;
run;

       Prob     Prob
Obs     Id     ImgId    var1    max    min    any_gt1    all_gt1

 1       1       3        0      1      0        0          0
 2       1       2        1      1      0        0          0
 3       1       1        1      1      0        0          0
 4       2       2        2      2      1        1          0
 5       2       1        1      2      1        1          0
 6       3       2        2      3      2        1          1
 7       3       1        2      3      2        1          1
 8       3       4        3      3      2        1          1
 9       3       3        2      3      2        1          1

[Zakharkou]

It works. Thank You very much!

[mkeintz]

Read each PROBID twice, the first time to see if there are any instances of VAR1<1, the second time to output the wanted data with the validated value of VAR2:

data have;
input ProbId ProbImgId var1;
cards;
1 1 1
1 2 1
1 3 0
2 1 1
2 2 2
3 1 2
3 2 2  
3 3 2
3 4 3
;
data want;
  set have (in=firstpass)
      have (in=secondpass);
  by probid;
  if first.probid then var2=1;
  retain var2;
  if firstpass=1 and var1<1 then var2=0;
  if secondpass;
run;