topic Re: CUMULATIVE SUM in SAS Programming

CUMULATIVE SUM

Tecla1 — Tue, 23 Oct 2018 08:26:49 GMT

Good morning, I have a big table and I need to make a cumulative sum with relative percent, like this :

type	date	amount	cumulative	cumulative perc.
1	01/02/2018	500	500	35,71%
1	02/02/2018	200	700	50,00%
1	03/02/2018	700	1400	100,00%
2	01/02/2018	300	300	60,00%
2	02/02/2018	200	500	100,00%

Can you help me? Which function I have to use?

Many Thanks for your help.

Re: CUMULATIVE SUM

RW9 — Tue, 23 Oct 2018 09:02:01 GMT

Post test data in the form of a datastep in future please, as such this code is untested:

proc sql;
  create table inter as
  select a.*,
         b.tot
  from   have a
  left join (select type,sum(amount) as tot from have group by type) b
  on     a.type=b.type;
quit;

data want;
  set inter;
  retain cumulative;
  by type;
  cumulative=ifn(first.type,amount,sum(cumulative,amount));
  cumulative_perc=(cumulative/tot) * 100;
run;

Re: CUMULATIVE SUM

Kurt_Bremser — Tue, 23 Oct 2018 10:02:30 GMT

Or use a double do loop:

data want;
sum = 0;
do until(last.type);
  set have;
  by type;
  sum + amount;
end;
cumulative = 0;
do until(last.type);
  set have;
  by type;
  cumulative + amount;
  cum_percent = cumulative / sum;
  output;
end;
drop sum;
format cum_percent percent9.2;
run;

Re: CUMULATIVE SUM

Tecla1 — Tue, 23 Oct 2018 10:17:44 GMT

Hi, Tnks to all, now I try … I will give you a feed back.

Re: CUMULATIVE SUM

singhsahab — Tue, 23 Oct 2018 10:54:49 GMT


data want1;
set have;
by type;
retain cumulative;
if first.type then cumulative=amount;
else cumulative+amount;
run;

proc sql;
create table want as
select  type,date,amount,cumulative,(cumulative/max_amount)*100 format=percent8.2 as per from(
select type,date,amount,cumulative,max(cumulative)as max_amount from want1 group by type) order by type,cumulative ;
quit;

Re: CUMULATIVE SUM

Tecla1 — Tue, 23 Oct 2018 15:33:26 GMT

Hi ,

many tnks for your solution ,

have a good afternoon !!!

Tecla