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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.