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.
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
So you want to ser var2 = 1 only for ProbId= 3 and ProbImgId = 4?
Which value shall var2 get in the other cases?
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 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.
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.