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

## SELECT first rows by SUM values

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## 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

6 REPLIES 6
Tourmaline | Level 20

## Re: SELECT first rows by SUM values

what if you have rows

1

1

11

?

Tourmaline | Level 20

## 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;``````
Fluorite | Level 6

## Re: SELECT first rows by SUM values

We change the last row value. The output will be:
1
1
8
Tourmaline | Level 20

## 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

Tourmaline | Level 20

## 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;
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;``````
Fluorite | Level 6

## 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;
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 🙂

Discussion stats
• 6 replies
• 1163 views
• 3 likes
• 3 in conversation