Find the By group with values within a limit

[amanegm]

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.

[Kurt_Bremser · Accepted solution]

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;

[Ksharp]

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;

[Ksharp]

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;

[mkeintz]

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;