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.
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;
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;
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;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.