topic Re: proc sql for repeat calulations using a multilevel dataset in SAS Procedures

proc sql for repeat calulations using a multilevel dataset

dr2014 — Tue, 26 May 2015 21:57:18 GMT

Hi All,

I am working with a dataset with the following multilevel layout.

agencyid	unitid	itemnumber	answer
1	2	201	YES
1	2	202	YES
1	2	203	YES
1	2	204	YES
1	3	201	YES
1	3	202	YES
1	3	203	YES
1	3	204	YES
1	4	201	YES
1	4	202	NO
1	4	203	YES
1	4	204	YES

The agency id ranges from 1 to 42. There are several unitids within an agencyid. Each unitid will answer itemnumbers within a question. I need the number of units within an agency that have all 'yes' answers to all the itemnumbers 201 to 204.

For eg here the count of unique unitids that have a 'yes' response to all the itemnumbers is 2.

How can I improvise my code below to account for 'yes' responses to all the itemnumbers 201 to 204 ? The below code only gives me counts of unitids for any one of the itemnumbers with a yes answer.

proc sql;

create table c as

select agencyid,

count(distinct unitid)

as numerator

from test /* name of dataset*/

where answer='Yes' and itemnumber in (201,202,203,204)

group by agencyid;

quit;

Please advice. Thanks !

Re: proc sql for repeat calulations using a multilevel dataset

PGStats — Wed, 27 May 2015 01:37:28 GMT

You need a subquery :

proc sql;

create table want as

select agencyId, count(unitId) as nbUnits

from

(select agencyId, unitId

from test

where answer = "YES" and itemNumber in (201, 202, 203, 204)

group by agencyId, unitId

having count(distinct itemNumber)=4)

group by agencyId;

quit;

Re: proc sql for repeat calulations using a multilevel dataset

stat_sas — Wed, 27 May 2015 02:29:31 GMT

proc sql;

create table c as

select agencyid,unitid from have

group by agencyid,unitid

having sum(answer='YES')=4;

quit;

Re: proc sql for repeat calulations using a multilevel dataset

dr2014 — Wed, 27 May 2015 13:54:01 GMT

Hi PGStats and stat@sas,

Thanks for your help, but I am still not getting the right answer...

The results table based on my example has to look like below

agencyid	Unitid count
1	2
2	1
3	6

I used both of your syntax and came up with this improvisation to my code. However, I get 0 rows and 2 columns . I not sure whats wrong ? Please let me know.

proc sql;

create table c as

select agencyid,

count(distinct unitid)

as numerator

from test

where itemid in (201,202,203,204)

group by agencyid

having sum(answer='Yes')=4;

quit;

Re: proc sql for repeat calulations using a multilevel dataset

PGStats — Wed, 27 May 2015 15:16:10 GMT

When I use my code with your example data (which covers agencyId=1 only) I get :

agencyid nbUnits

------------------

1 2

In your new query, you group on agencyId and ask for the group condition sum(answer='Yes')=4 . You are thus selecting agencies (not units) with 4 YES answers. That's why you need a subquery. The lowest (subquery) level groups on agency and units to extract the units with 4 YES answers. The higher level counts the selected units within each agency.

Re: proc sql for repeat calulations using a multilevel dataset

dr2014 — Wed, 27 May 2015 16:12:06 GMT

Thanks PG for the explanation. That makes sense. Let me give it a try and I shall get back.

Re: proc sql for repeat calulations using a multilevel dataset

dr2014 — Wed, 27 May 2015 17:38:01 GMT

Thanks PG ! It worked. I had an error in my syntax ! I broke your code down and ran it step wise.