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.
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;
Not easily.
Proc freq will report these stats very easily.
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.