SELECT first rows by SUM values

[RoberJunior]

Hi there!

Consider the following problem:

TableA:

I would like to select the first rows that the sum of values is equal 10. Therefore, the output is:

Someone know how to do this in DATA SET or PROC SQL?

Thanks 🙂

[ChrisNZ]

what if you have rows

1

1

11

?

[novinosrin]

data have;
input id value;
datalines;
1 3
2 5
3 2
4 14
5 5
;

data _null_;
if _N_ = 1 then do;
	if 0 then set have;
   declare hash h(dataset:'have(obs=0)',ordered:'y');
   h.defineKey('id');
   h.defineData('id','value');
   h.defineDone();
end;
set have;
_value+value;
h.add();
if _value>=10 then do;h.output(dataset:'want');stop;end;
run;

[RoberJunior]

We change the last row value. The output will be:
1
1
8

[ChrisNZ · Accepted solution]

Like this?

data HAVE;
input ID VALUE;
datalines;
1 3
2 5
4 14
5 5
run;
data WANT;
  set HAVE;
  SUM+VALUE;
  if SUM>=10 then do;
    VALUE+-SUM+10;
    output; 
    stop;
  end;
  else output;
run;

ID	VALUE	SUM
1	3	3
2	5	8
4	2	22

[novinosrin]

data _null_;
if _N_ = 1 then do;
	if 0 then set have;
   declare hash h(dataset:'have(obs=0)',ordered:'y');
   h.defineKey('id');
   h.defineData('id','value');
   h.defineDone();
end;
set have;
_k=_value;_value+value;
if _value<=10 then h.add();
else if _value=10 then do;h.output(dataset:'want');stop;end;
else if _value>10 then do;value=value-_k-1;h.add();h.output(dataset:'want');stop;end;
run;

[RoberJunior]

Thanks for help me guys 🙂

Now, I want to develop a new step. Consider this example:

TABLE 01:

I want the declare a variable X that is sum VALUES if CLASS = A. Therefore, X = 10

Then will run the code with variable X:

data _null_;
if _N_ = 1 then do;
	if 0 then set have;
   declare hash h(dataset:'have(obs=0)',ordered:'y');
   h.defineKey('id');
   h.defineData('id','value');
   h.defineDone();
end;
set have;
_k=_value;_value+value;
if _value<=X then h.add();
else if _value=X then do;h.output(dataset:'want');stop;end;
else if _value>X then do;value=value-_k-1;h.add();h.output(dataset:'want');stop;end;
run;

Can you guys help to develop the first step, plz?

Thanks 🙂