Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Where Funktion with the selection of all the variables in a subgroup

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-02-2022 02:35 PM
(1788 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Which value shall var2 get in the other cases?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

In other cases, can we set the Variable var2=0.

Thanks a lot.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

It works. Thank You very much!

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--------------------------

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

--------------------------

**SAS Innovate 2025** is scheduled for May 6-9 in Orlando, FL. Sign up to be **first to learn** about the agenda and registration!

What is Bayesian Analysis?

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.

Ready to level-up your skills? Choose your own adventure.