topic Re: Conditional sum in SAS Programming

Conditional sum

David_Billa — Mon, 15 Jun 2020 11:45:35 GMT

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

Excepted Output:

VALUE:

100

Re: Conditional sum

Kurt_Bremser — Mon, 15 Jun 2020 11:54:48 GMT

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;

Re: Conditional sum

PeterClemmensen — Mon, 15 Jun 2020 11:57:31 GMT

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;

Re: Conditional sum

David_Billa — Mon, 15 Jun 2020 12:01:06 GMT

@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?

Re: Conditional sum

Jagadishkatam — Mon, 15 Jun 2020 12:06:17 GMT

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;

Re: Conditional sum

novinosrin — Mon, 15 Jun 2020 12:13:23 GMT

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;

Re: Conditional sum

David_Billa — Mon, 15 Jun 2020 12:17:15 GMT

@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?

Re: Conditional sum

Jagadishkatam — Mon, 15 Jun 2020 12:21:30 GMT

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;

Re: Conditional sum

David_Billa — Mon, 15 Jun 2020 12:26:11 GMT

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

Re: Conditional sum

Kurt_Bremser — Mon, 15 Jun 2020 12:29:02 GMT

@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.

Re: Conditional sum

Jagadishkatam — Mon, 15 Jun 2020 12:29:12 GMT

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.

Re: Conditional sum

MCoopmans — Mon, 15 Jun 2020 12:44:26 GMT

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.

Re: Conditional sum

novinosrin — Mon, 15 Jun 2020 12:54:05 GMT

HI @David_Billa I do not have clue of what is DI studio, however noticing the mention by @MCoopmans DI uses SQL, you could perhaps attempt to use the SQL solution below in your DI studio. Would the below help?


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


proc sql;
create view temp as
select *,monotonic() as rn
from have;
create table want as
select sum(b.value) as sum
from temp a left join temp b
on b.rn>=a.rn
group by a.rn;
quit;

Re: Conditional sum

David_Billa — Mon, 15 Jun 2020 13:06:42 GMT

@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.

Re: Conditional sum

MCoopmans — Mon, 15 Jun 2020 13:13:52 GMT

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;

Re: Conditional sum

David_Billa — Mon, 15 Jun 2020 13:16:54 GMT

Great! How to tweak your code so that I don't end up with creating/renaming the variable which holds sum value (VALUE) and also have to group by on three variables?

Re: Conditional sum

novinosrin — Mon, 15 Jun 2020 13:23:14 GMT

to hold sum value as value, there isn't much to do, except

select sum(b.value) as value

Not sure, what you mean by- and also have to group by on three variables? Do you mean something like

select x,y,z,sum(value) as value


group by a.rn, x,y,z;

Re: Conditional sum

David_Billa — Mon, 15 Jun 2020 13:39:20 GMT

yes, I want like you mentioned in my previous post but it is not producing the desired Output as it is summing the values from all the observations and then doing Group by instead sum values only from current observations and do the Group by

Re: Conditional sum

David_Billa — Mon, 15 Jun 2020 13:45:22 GMT

Still same error.

Re: Conditional sum

David_Billa — Mon, 15 Jun 2020 13:54:12 GMT

If you group by row number then you will get the same results as your
source dataset