Find the By group with values within a limit

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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
Esteemed Advisor
Posts: 6,646

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

View solution in original post


All Replies
Solution
‎12-08-2016 07:24 AM
Esteemed Advisor
Posts: 6,646

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
Grand Advisor
Posts: 9,576

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;
Grand Advisor
Posts: 9,576

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: 789

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 218 views
  • 0 likes
  • 4 in conversation