turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- proc sql performing repeat calculations and creat...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2015 04:55 PM

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 !

Accepted Solutions

Solution

05-04-2015
05:48 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dr2014

05-04-2015 05:48 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dr2014

05-04-2015 05:48 PM

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,

**100**) as proportion

from test1

where questionid in ('Q1','Q2','Q3')

**group by agencyid**;

quit;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dr2014

05-04-2015 07:34 PM

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

select distinct agencyid, sum(answer='yes') as

yes_count, sum(answer='yes')/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=&var.;

quit;

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

%end;

%mend aid;

%aid(%str(18 19 36))

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dr2014

05-05-2015 09:41 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dr2014

05-05-2015 11:25 AM

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,

**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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to dr2014

05-05-2015 12:41 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to slchen

05-05-2015 02:26 PM

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 ?