topic Count number of distinct cases meeting different criteria in SAS Procedures

Count number of distinct cases meeting different criteria

lizzy28 — Fri, 31 Oct 2014 16:54:10 GMT

I would like to count numbers of distinct cases that meet different criteria in my large dataset. The example data looks like below:

obsid	var
1	a
1	b
1	b
2	b
2	b
2	c
2	c
3	a
3	a
4	c

I need to see how many distinct obsids that contain "a", "b" or "c" in var.

I could do for different criteria one after another like this:

proc sql; select count(distinct obsid) as N_a from project.dswi_nodebride where var="a"; run;

proc sql; select count(distinct obsid) as N_b from project.dswi_nodebride where var="b"; run;

proc sql; select count(distinct obsid) as N_c from project.dswi_nodebride where var="c"; run;

I have many criteria, so is there a simpler way to do for all different but similar criteria?

Thanks a lot!

Re: Count number of distinct cases meeting different criteria

stat_sas — Fri, 31 Oct 2014 17:04:11 GMT

proc sql;

select var,count(distinct obsid) as N from have

group by var;

quit;

Re: Count number of distinct cases meeting different criteria

PGStats — Fri, 31 Oct 2014 17:05:12 GMT

SQL is ideal for this:

data have;

input obsid var$;

datalines;

1 a

1 b

2 b

2 c

3 a

4 c

;

proc sql;

create table want as

select var, count(distinct obsid) as n

from have

group by var;

select * from want;

quit;

Re: Count number of distinct cases meeting different criteria

data_null__ — Fri, 31 Oct 2014 17:13:26 GMT

This is somewhat like counting subjects with adverse events. This counts events and obsids for each level of var.

data ae;
   infile cards expandtabs; 
   input obsid var :$1.; 
   cards; 
1  a
1  b
1  b
2  b
2  b
2  c
2  c
3  a
3  a
4  c
;;;;
   run; 
proc print; 
   run; 
proc summary data=ae nway; 
   class var obsid;
   output out=events(drop=_type_ rename=(_freq_=events));
   run; 
proc print; 
   run; 
proc summary data=events nway; 
   class var;
   output out=obsids(drop=_type_ rename=(_freq_=obsids)) sum(events)=;
   run; 
proc print; 
   run;

Re: Count number of distinct cases meeting different criteria

lizzy28 — Fri, 31 Oct 2014 18:53:06 GMT

Thanks so much to you all!

Lizi