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.
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;
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;
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;
Hi, Tnks to all, now I try … I will give you a feed back.
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;
Hi ,
many tnks for your solution ,
have a good afternoon !!!
Tecla
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.