## Count number of distinct cases meeting different criteria

Solved
Frequent Contributor
Posts: 110

# Count number of distinct cases meeting different criteria

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!

Accepted Solutions
Solution
‎10-31-2014 01:05 PM
Posts: 5,524

## Re: Count number of distinct cases meeting different criteria

SQL is ideal for this:

data have;

input obsid var\$;

datalines;

1 a

1 b

1 b

2 b

2 b

2 c

2 c

3 a

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;

PG

PG

All Replies
Posts: 1,270

## Re: Count number of distinct cases meeting different criteria

proc sql;

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

group by var;

quit;

Solution
‎10-31-2014 01:05 PM
Posts: 5,524

## Re: Count number of distinct cases meeting different criteria

SQL is ideal for this:

data have;

input obsid var\$;

datalines;

1 a

1 b

1 b

2 b

2 b

2 c

2 c

3 a

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;

PG

PG
Posts: 3,852

## Re: Count number of distinct cases meeting different criteria

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;

Frequent Contributor
Posts: 110

## Re: Count number of distinct cases meeting different criteria

Thanks so much to you all!

Lizi

🔒 This topic is solved and locked.