Hi Gregely and slchen, Although Gregely's suggestion worked. I have one more calculation I need to make. I also need a column with an average value of the proportions. I am unable to use the avg function when I use the 'group by' option and have to perform another proc sql to get that column. Is there a way to do a subquery within the original SQL. I basically want the table to look like this now : Group1: agencyid yes_count numerator denominator proportion avg_proportion 18 ## ### ### ### ### 19 ## ### ### ### 36 ## ### ### ### proc sql; create table p as select agencyid, count(case answer when 'yes' then 1 else 0 end) as yes_count, ((count(case answer when 'yes' then 1 else 0 end))/24) as numerator, count(distinct unitid) as denominator, ((calculated numerator/calculated denominator)*100) as proportion from test1 where questionid in ('Q1','Q2','Q3') group by agencyid; quit; proc sql; create table q as select agencyid , yes_count, numerator, denominator, proportion, avg(proportion) as grp_avg, from p, quit; Also with this code , the average value gets repeated for each row in the table. Please let me know.
... View more
