Hi All, I have to calculate proportions of 'yes' answers for group1. In group1 there are multiple agencies. Within each agency (represented by agencyid ) are several units (represented by unitid). For a survey conducted for the group1, I need to calculate the proportion of 'yes' answers for a set of questions (represented as questionid ) across all units in an agency. Calculation is as follows: Numerator = (Sum of all 'yes' answers by all units in the agency / 24)) * 100 Denominator number of units within the agency 24 represents total number of questions Here is my code : proc sql; create table p as select distinct 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') and agencyid=18; quit; I want to repeat this calculation for other agencyid's in the group. However, the values are not consecutive. For example , for this group1, the agencyid's are 18,19 and 36. I offcourse knew these 3 agencies by running a proc freq. Instead of doing that, I would like to create a generic code where the calculation starts with the minimum value of the agencyid and ends with the maximum value and ignores the non-existing values between 19 to 36. It should create a table (just like table p -mentioned above) , but also include calculations for other agencies such as : Group1: agencyid yes_count numerator denominator proportion 18 ## ### ### ### 19 ## ### ### ### 36 ## ### ### ### I also created a keyword macro , but it will be very tedious to then stack the individual datasets especially for groups with plenty of agencies. here is that code: %let v1=18; %let v2=19; %let v3=36; %macro aid (v); proc sql; create table p&v. as select distinct 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','Q3','Q5') and agencyid=&v.; %end; quit; %mend faid; %faid (&v1); %faid (&v2); %faid (&v3); Please advice and let me know your thoughts. Thanks !
... View more
