BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
amanegm
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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;

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User
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
Super User
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
Super User

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
PROC Star
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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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