topic Where Funktion with the selection of all the variables in a subgroup in SAS Procedures

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

Zakharkou — Sat, 02 Jul 2022 18:36:44 GMT

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.

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

Kurt_Bremser — Sat, 02 Jul 2022 19:21:24 GMT

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

Which value shall var2 get in the other cases?

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

Zakharkou — Sat, 02 Jul 2022 19:38:41 GMT

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.

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

Tom — Sat, 02 Jul 2022 20:50:41 GMT

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.

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

Zakharkou — Sat, 02 Jul 2022 20:58:48 GMT

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

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

Tom — Sat, 02 Jul 2022 21:06:11 GMT

@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.

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

Zakharkou — Sat, 02 Jul 2022 21:13:55 GMT

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

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

Tom — Sat, 02 Jul 2022 21:28:54 GMT

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

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

Tom — Sat, 02 Jul 2022 21:36:13 GMT

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

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

mkeintz — Sat, 02 Jul 2022 22:30:06 GMT

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;

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

Zakharkou — Sun, 03 Jul 2022 15:43:07 GMT

It works. Thank You very much!