CODEWITHSUNDEEP
countsas
PinkyL
PinkyL

Reputation: 361

proc sql count by two groups or variables

I have some data that looks like this:

  ID   flag
    A        1
    A        1
    A        2
    B        1    
    B        2
    B        3

How do I use proc sql to count the Id by flag so that the outcome looks like this?:

ID   flag  count
A       1     2
A       2     1
B       1     1
B       2     1
B       3     1

The query I used does not seem to be correct since it returns the distinct types of flag, not how many of each type of flag there are.

proc sql;
select Id, flag, count(flag) as count from table
group by Id;

Upvotes: 0

Views: 11217

Answers (1)

Joe
Joe

Reputation: 63434

You need to include flag in your grouping.

proc sql;
  select Id, flag, count(1) as count from table
    group by Id, flag;
quit;

Upvotes: 3

Related Questions