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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.