DATA Step, Macro, Functions and more

Proc Sql Cumulative Percent

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

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: 9,662

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;



View solution in original post


All Replies
Super User
Posts: 17,734

Re: Proc Sql Cumulative Percent

Not easily. 

 

Proc freq will report these stats very easily. 

Respected Advisor
Posts: 4,640

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: 9,662

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 671 views
  • 2 likes
  • 4 in conversation