Help using Base SAS procedures

Count number of distinct cases meeting different criteria

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 98
Accepted Solution

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:

obsidvar
1a
1b
1b
2b
2b
2c
2c
3a
3a
4c

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
Respected Advisor
Posts: 4,925

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

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
Respected Advisor
Posts: 4,925

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
Respected Advisor
Posts: 3,799

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;

10-31-2014 12-12-12 PM.png
Frequent Contributor
Posts: 98

Re: Count number of distinct cases meeting different criteria

Posted in reply to data_null__

Thanks so much to you all!

Lizi

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 589 views
  • 6 likes
  • 4 in conversation