topic Proc Sql Cumulative Percent in SAS Programming

Proc Sql Cumulative Percent

Siddharth123 — Sat, 24 Sep 2016 14:42:31 GMT

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.

Re: Proc Sql Cumulative Percent

Reeza — Sat, 24 Sep 2016 16:38:54 GMT

Not easily.

Proc freq will report these stats very easily.

Re: Proc Sql Cumulative Percent

PGStats — Sat, 24 Sep 2016 20:59:07 GMT

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;

Re: Proc Sql Cumulative Percent

Ksharp — Sun, 25 Sep 2016 02:32:54 GMT

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;