Solved
New Contributor
Posts: 3

# SELECT first rows by SUM values

[ Edited ]

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

Accepted Solutions
Solution
‎02-02-2018 07:08 PM
Super User
Posts: 2,512

## Re: SELECT first rows by SUM values

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

All Replies
Super User
Posts: 2,512

## Re: SELECT first rows by SUM values

what if you have rows

1

1

11

?

Super User
Posts: 2,061

## Re: SELECT first rows by SUM values

``````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;
if _value>=10 then do;h.output(dataset:'want');stop;end;
run;``````
New Contributor
Posts: 3

## Re: SELECT first rows by SUM values

We change the last row value. The output will be:
1
1
8
Solution
‎02-02-2018 07:08 PM
Super User
Posts: 2,512

## Re: SELECT first rows by SUM values

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

Super User
Posts: 2,061

## Re: SELECT first rows by SUM values

``````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;
else if _value=10 then do;h.output(dataset:'want');stop;end;
run;``````
New Contributor
Posts: 3

## Re: SELECT first rows by SUM values

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;
else if _value=X then do;h.output(dataset:'want');stop;end;
run;``````

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

Thanks

☑ This topic is solved.