Solved
Contributor
Posts: 57

# Proc Sql Cumulative Percent

Hi All,

I am using code below -

proc sql;
create table namescnt as select a.*, Count/sum(Count) as pct
from test a
group by Gender, Year
ORDER BY Count Desc;
quit;

I am wondering if there is a way to calculate the cumulative % (last field in excel) within Proc Sql above for Gender & Year Group. Please see attached excel for example.

Accepted Solutions
Solution
‎09-25-2016 06:46 AM
Super User
Posts: 10,770

## Re: Proc Sql Cumulative Percent

```Yeah.That is really not easy. You need create an index variable.

data have;
input gender \$ year count;
cards;
F 2011 34
F 2011 34
F 2012 21
F 2012 34
F 2014  4
M 2010  34
M 2011 34
M 2011   45
M 2012  2
M 2012 34
M 2012 34
M 2012 34
;
run;
data have;
set have;
by gender;
if first.gender then n=0;
n+1;
run;
proc sql;
select a.*,count/sum(count) as per,
(select sum(count) from have where gender=a.gender and year=a.year and n le a.n)/sum(count) as cumper
from have as a
group by gender,year
order by gender,year,n;
quit;

```

All Replies
Super User
Posts: 23,694

## Re: Proc Sql Cumulative Percent

Not easily.

Proc freq will report these stats very easily.

Posts: 5,523

## Re: Proc Sql Cumulative Percent

Any processing that requires sequential operations will be easier to do with a datastep :

Assuming dataset test is properly sorted...

``````data want;
do until(last.year);
set test; by gender year;
cumCount = sum(cumCount, count);
end;
cum = 0;
do until(last.year);
set test; by gender year;
pct = count / cumCount;
cum + count;
cumpct =  cum / cumCount;
output;
end;
drop cumCount cum;
run;``````
PG
Solution
‎09-25-2016 06:46 AM
Super User
Posts: 10,770

## Re: Proc Sql Cumulative Percent

```Yeah.That is really not easy. You need create an index variable.

data have;
input gender \$ year count;
cards;
F 2011 34
F 2011 34
F 2012 21
F 2012 34
F 2014  4
M 2010  34
M 2011 34
M 2011   45
M 2012  2
M 2012 34
M 2012 34
M 2012 34
;
run;
data have;
set have;
by gender;
if first.gender then n=0;
n+1;
run;
proc sql;
select a.*,count/sum(count) as per,
(select sum(count) from have where gender=a.gender and year=a.year and n le a.n)/sum(count) as cumper
from have as a
group by gender,year
order by gender,year,n;
quit;

```
☑ This topic is solved.