## proc sql performing repeat calculations and creating a table of the results

Solved
Frequent Contributor
Posts: 146

# proc sql performing repeat calculations and creating a table of the results

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);

Thanks !

Accepted Solutions
Solution
‎05-04-2015 05:48 PM
SAS Employee
Posts: 340

## Re: proc sql performing repeat calculations and creating a table of the results

Would this worki?:

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;

All Replies
Solution
‎05-04-2015 05:48 PM
SAS Employee
Posts: 340

## Re: proc sql performing repeat calculations and creating a table of the results

Would this worki?:

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;

Super Contributor
Posts: 275

## Re: proc sql performing repeat calculations and creating a table of the results

Not sure whether this is what you need:

%macro aid (v);

%let i=1;

%do %while (%scan(&v,&i)ne);

%let var=%scan(&v,&i);

proc sql;

create table p&var. as

count(distinct unitid) as denominator, ((calculated numerator/calculated denominator)*100) as proportion

from test1

where questionid in ('Q1','Q3','Q5') and agencyid=&var.;

quit;

%let i=%eval(&i+1);

%end;

%mend aid;

%aid(%str(18 19 36))

Frequent Contributor
Posts: 146

## Re: proc sql performing repeat calculations and creating a table of the results

Thanks Gergely. That worked. I didn't think about the 'group by' ! @slchen...I was wanting to avoid the using keyword names. But, will try this for another scenario. Thanks for the suggestion on the 'sum' function instead of count.

Frequent Contributor
Posts: 146

## Re: proc sql performing repeat calculations and creating a table of the results

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.

Super Contributor
Posts: 275

## Re: proc sql performing repeat calculations and creating a table of the results

Try this:

proc sql;

create table p as

select *, avg(proportion) as grp_avg from(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;

Frequent Contributor
Posts: 146

## Re: proc sql performing repeat calculations and creating a table of the results

Thanks slchen. It did work. I have one more question. I wanted to have only the first row of the table to mention the value for grp_avg though. Would I need to create an id and then specify that.. id from 2 to eof grp_avg='   '. What would be the best way to accomplish that ?

🔒 This topic is solved and locked.