Calcite | Level 5

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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
10 REPLIES 10
Super User

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

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

Which value shall var2 get in the other cases?

Calcite | Level 5

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

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.
Super User

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

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.

Calcite | Level 5

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

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

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

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

Calcite | Level 5

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

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

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

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
Super User

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

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
Calcite | Level 5

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

It works. Thank You very much!

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

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Discussion stats
• 10 replies
• 437 views
• 0 likes
• 4 in conversation