DATA Step, Macro, Functions and more

SELECT first rows by SUM values

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

SELECT first rows by SUM values

[ Edited ]

Hi there!

 

Consider the following problem:

 

TableA:

1.png

 

 

 

 

 

 

 

 

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

 

2.png

 

 

 

 

 

 

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

 

Thanks Smiley Happy


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

Re: SELECT first rows by SUM values

Posted in reply to RoberJunior

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

 

View solution in original post


All Replies
Super User
Posts: 2,512

Re: SELECT first rows by SUM values

Posted in reply to RoberJunior

what if you have rows

1

1

11

?

Super User
Posts: 2,061

Re: SELECT first rows by SUM values

Posted in reply to RoberJunior
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;
New Contributor
Posts: 3

Re: SELECT first rows by SUM values

Posted in reply to RoberJunior
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

Posted in reply to RoberJunior

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

Posted in reply to RoberJunior
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;
New Contributor
Posts: 3

Re: SELECT first rows by SUM values

Posted in reply to novinosrin

Thanks for help me guys Smiley Happy

 

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

 

TABLE 01:
1.png

 

 

 

 

 

 

 

 

 

 

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 Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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