Conditional sum

[David_Billa]

I've the data as follows. For every record I've to sum of all the records from current observation to till last observation but not from previous observation. Any help?

VALUE

20

20

20

20

20

Excepted Output:

VALUE:

100

80

60

40

20

[Kurt_Bremser]

Sort in reverse order, sum with a retained variable, then sort back.

Or, to avoid the first sort, do this:

data sum;
value = 0;
do _n_ = nobs to 1;
  set have (rename=(value=v)) nobs=nobs point=_n_;
  value + v;
  n = _n_;
  output;
end;
stop;
drop v;
run;

proc sort
  data=sum
  out=want (drop=n)
;
by n;
run;
  

[PeterClemmensen]

How about

data have;
input VALUE;
datalines;
20
20
20
20
20
;

data want;
    do until (lr1);
        set have end=lr1;
        s = sum(s, value);
    end;
    output;
    do until (lr2);
        set have(firstobs=2) end=lr2;
        s = s - value;
        output;
    end;
run;

[David_Billa]

@PeterClemmensen @Kurt_Bremser Thanks for the code. I want to implement this logic in DI Studio under Expression. Any ways to tackle it in case when Statement or something similar instead of do loop?

[Jagadishkatam]

without the do loop you can try below code

data want;
set have;
sum+value;
run;

proc sort data=want;
by descending sum value;
run;
 

[David_Billa]

Thanks. What is that 'sum' in datastep?
What it does?

[Jagadishkatam]

Sum is the variable name , if we keep sum+value, it is do the summation in a cumulative way by retaining the values. So we do not have to retain the variable separately.

[Kurt_Bremser]

---

@David_Billa wrote:

@PeterClemmensen @Kurt_Bremser Thanks for the code. I want to implement this logic in DI Studio under Expression. Any ways to tackle it in case when Statement or something similar instead of do loop?

---

I don't work with DI Studio, but if it has a node for calculating a cumulative sum, then you can do the sort(descending)-cumulation-sort as I suggested in my first post.

[Jagadishkatam]

Alternatively try below code

data have;
input VALUE;
cards;
20
20
20
20
20
;

data want;
do until(end);
set have end=end;
retain sum;
sum+value;
output;
end;
run;

proc sort data=want;
by descending sum value;
run;
 

[David_Billa]

@Jagadishkatam @novinosrin Thanks for the code. I want to implement this logic in DI Studio under Expression. Any ways to tackle it in case when Statement or something similar instead of do loop?

[novinosrin]

Hi @David_Billa  

data have;
input VALUE;
cards;
20
20
20
20
20
;

data want ;
  dcl hash H (ordered: "A") ;
  h.definekey  ("_n_") ;
  h.definedata ("value") ;
  h.definedone () ;
  do _n_=1 by 1 until(z);
   set have end=z;
   h.add();
  end;
  z=0;
  dcl hiter hi('h');
  do _n_=1 by 1 until(z);
   set have end=z;
   do _n=hi.setcur() by 0 while(_n=0);
    sum=sum(value,sum);
	_n=hi.next();
   end;
   output;
   sum=.;
  end;
  stop;
  keep sum;
run;

[MCoopmans]

I think that in DI studio the generated code will be in SQL , and I don't think it will be able to calculate a cumulative sum.

DI Studio does allow to order the table first, then add a uer writen code transformation based on what @Jagadishkatam has proposed, but you can put macrovariables in that will automatically be changed to the dataset names that you connect to this user written transforms.

data &_OUTPUT; 
  set &SYSLAST; 
  sum+value;
run;

You can find similar examples in the online documentation of DI Studio.

[David_Billa]

@MCoopmans @Jagadishkatam I tried this code below but it's failing. Error which I got is,

W7F5UFE is a source dataset.

 ERROR: Cannot sort in place. - ERROR: Unable to create WORK.W7F5UFE.DATA because WORK.W7F5UFE.VIEW already exists. - ERROR: BY variables are not properly sorted on data set WORK.W7F5UFE

proc sort data=&SYSLAST;
by _all_;
run;

 /*sum and group by of three variables var1 var 2 var3*/
data &_OUTPUT; 
  set &SYSLAST; 
  sum+VALUE;
  by var 1 var2 var3;
run;

proc sort data=&_OUTPUT;
by descending sum VALUE;
run;

Objective is to sum the values from VALUE field as I mentioned in the post and Group of the three variables var1,var2 and var3. I don't want any new field in the final target table as well. Kindly guide me to resolve the error.

[MCoopmans]

The sort is failing to overwrite the view that was created in the previous step, give this one a try instead (just adding out=_tempsort):

proc sort data=&SYSLAST out=_tempsort;
by _all_;
run;

/*sum and group by of three variables var1 var 2 var3*/ data &_OUTPUT; set &SYSLAST; sum+VALUE; by var 1 var2 var3; run; proc sort data=&_OUTPUT; by descending sum VALUE; run;

[David_Billa]

Still same error.