creating a frequency table

[iced_tea]

Example:

customer_id	movie
12345	Rocky
12345	Jaws
12345	Titanic
98765	Jurassic Park
75319	Titanic
75319	The Dark Knight
35712	Legally Blonde

There are 4 distinct customer_ids in this table. How can I get the desired output below?

# of movies	# of customers
1	2
2	1
3	1

Where I have the number of movies in one column, and the number of customers with corresponding number of movies in the second column.

Thanks!

[ballardw · Accepted solution]

---

@iced_tea wrote:

Example:

customer_id	movie
12345	Rocky
12345	Jaws
12345	Titanic
98765	Jurassic Park
75319	Titanic
75319	The Dark Knight
35712	Legally Blonde

There are 4 distinct customer_ids in this table. How can I get the desired output below?

# of movies	# of customers
1	2
2	1
3	1

Where I have the number of movies in one column, and the number of customers with corresponding number of movies in the second column.

Thanks!

---

One way:

proc freq data=have noprint;
   table customer_id/out=temp (rename=(count=NumMovies));
run;

proc freq data=temp noprint;
   table nummovies /out= want(rename=(count=numcustomers) drop=percent);
run;

The first proc freq counts how many movies each customer sees. Then you count how many of the counted movies seen tells how many customers see how many movies.

The rename is to get variables close to yours which are not standard variable names. Assign labels if you want different text to appear in reports.

[Ksharp]

proc sql;
select movie,count(distinct customer_id ) as number_customer
from have
group by movie ;
quit;

[iced_tea]

Thanks for taking a look. I think this creates a table of unique "Movie" and the # of customers who watched that specific movie.
But what I want instead is a table where we count the # of customers who watched 1 movie, 2 movies, 3 movies, etc.

[Ksharp]

OK . Need some more code.

proc sql;
select number_movie,count(*) as number_customer from
(
select customer_id,count(distinct movie ) as number_movie
from have
group by customer_id
)
group by number_movie;
quit;