Count Frequency of subgroup Proc SQL

Question

I am trying to find a way to select a frequency count for rows of a subgroup with no distinct identifiers (well, I guess the distinct identifier is a combination of statuses). Consider the sample data:

data have;
    input Series $ Game Name $ Points;
datalines;
A 1 LeBron 2
A 1 LeBron 3
A 1 LeBron 2
A 1 LeBron 2
A 2 LeBron 2
A 2 LeBron 2
A 2 LeBron 3
A 3 LeBron 2
;
run;

Each row here is a shot LeBron took in a game within a series. I want The series/game summary, with a count for number of shots. Like this:

Series Game Name     Freq Sum 2pt 3pt
A      1    LeBron   4    9   3   1
A      2    LeBron   3    7   2   1
A      3    LeBron   1    2   1   0

I have to use Proc SQL here, rather then proc means because I am pulling the data in from multiple tables. Also, I will have several thousand "Series" and many more "Games" and "Names" so please keep answer general Here is what I have:

proc sql;
    create table want as
    select Series,
                Game,
                Name,
                sum(points) as totalpoints
    from have
    group by 1,2,3;
run;

Thanks.

Pyll

Joe Joe · Accepted Answer · 2014-07-09T14:02:16

No particular reason you couldn't use PROC MEANS pulling from multiple tables - you can always create a view (either in SQL or in the data step). But anyway,

proc sql;
    create table want as
    select Series,
                Game,
                Name,
                sum(points) as totalpoints,
                count(points) as numbershotsmade
    from have
    group by 1,2,3;
run;

You can also use the n function which does the same thing.

count(points) will count the non-null points values; count(1) will count the total number of rows even if points is null.

Count Frequency of subgroup Proc SQL

1 Answers