## Find the By group with values within a limit

Solved
Occasional Contributor
Posts: 7

# Find the By group with values within a limit

Hi SAS Users,

I am not able to find the ID which has negative values within the BY group. Let me explain. My data looks like below:

ID            Value

100         1792

100         68193

100         -66173

100         1012

100         -366

101         -580

101          296

101         -37

102         13008

102         -205

102         2808

102         1185

So I want to identify all such ID's which, having one or more negative values within the BY group, then all should be within the range -1000 to -1.

Here ID 100 has a value -366 but it also has -66173. So I dont want to select this. All the negative values within the BY group should be within the above limit.

ID 101 has two negative values -580 & -37 and both lie in the limit so I will select ID 101.

Also, for ID 102 it has only one negative value, -205. Hence I will select it as it lies in the given range.

Further, I need to differentiate between the selected ID's which have one or more negative values within the limit. Count of negative values within the limit would be helpful.

Accepted Solutions
Solution
‎12-08-2016 07:24 AM
Super User
Posts: 10,284

## Re: Find the By group with values within a limit

``````data want (keep=id negcount);
set have;
by id;
retain negflag largenegflag;
if first.id
then do;
negflag = 0;
largenegflag = 0;
negcount = 0;
;
if -1000 <= value <= -1
then do;
negflag = 1;
negcount + 1;
end;
if value < -1000 then largenegflag = 1;
if last.id and negflag and not largenegflag then output;
run;
``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎12-08-2016 07:24 AM
Super User
Posts: 10,284

## Re: Find the By group with values within a limit

``````data want (keep=id negcount);
set have;
by id;
retain negflag largenegflag;
if first.id
then do;
negflag = 0;
largenegflag = 0;
negcount = 0;
;
if -1000 <= value <= -1
then do;
negflag = 1;
negcount + 1;
end;
if value < -1000 then largenegflag = 1;
if last.id and negflag and not largenegflag then output;
run;
``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,788

## Re: Find the By group with values within a limit

data have; input ID Value; cards; 100 1792 100 68193 100 -66173 100 1012 100 -366 101 -580 101 296 101 -37 102 13008 102 -205 102 2808 102 1185 ; run; proc sql; select distinct id from have group by id having sum(value lt -1000)=0 and sum(value between -1000 and -1) ne 0; quit;
Super User
Posts: 10,788

## Re: Find the By group with values within a limit

```
data have;
input ID            Value;
cards;
100         1792
100         68193
100         -66173
100         1012
100         -366
101         -580
101          296
101         -37
102         13008
102         -205
102         2808
102         1185
;
run;
proc sql;
select distinct id
from have
group by id
having sum(value lt -1000)=0 and sum(value between -1000 and -1) ne 0;
quit;

```
Posts: 1,346

## Re: Find the By group with values within a limit

This is a good task for taking advantage of multiple datasets in a SET statememt. The SET statement below will insure that all the negative values greater/equal to -1000 will precede all values< -1000, so a test of value at last.id is sufficient:

data want;
retain negcount 0;
set have (where=(-1000<=value<0))
have (where=(value<-1000));
by ID;
if first.id then negcount=0;
if value>= -1000 then negcount+1;
if last.id & value>=-1000;
run;
☑ This topic is solved.