topic Re: SELECT first rows by SUM values in SAS Programming

SELECT first rows by SUM values

RoberJunior — Fri, 02 Feb 2018 00:47:18 GMT

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 🙂

Re: SELECT first rows by SUM values

ChrisNZ — Fri, 02 Feb 2018 02:06:50 GMT

what if you have rows

Re: SELECT first rows by SUM values

novinosrin — Fri, 02 Feb 2018 02:15:37 GMT

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;

Re: SELECT first rows by SUM values

RoberJunior — Fri, 02 Feb 2018 02:26:36 GMT

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

Re: SELECT first rows by SUM values

ChrisNZ — Fri, 02 Feb 2018 02:35:17 GMT

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

Re: SELECT first rows by SUM values

novinosrin — Fri, 02 Feb 2018 02:59:44 GMT

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;

Re: SELECT first rows by SUM values

RoberJunior — Sat, 03 Feb 2018 00:21:30 GMT

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 🙂