topic Re: creating a frequency table in New SAS User

creating a frequency table

iced_tea — Mon, 21 Jun 2021 22:59:51 GMT

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!

Re: creating a frequency table

ballardw — Mon, 21 Jun 2021 23:15:17 GMT

@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.

Re: creating a frequency table

Ksharp — Tue, 22 Jun 2021 12:11:40 GMT

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

Re: creating a frequency table

iced_tea — Tue, 22 Jun 2021 14:19:23 GMT

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.

Re: creating a frequency table

Ksharp — Wed, 23 Jun 2021 11:45:26 GMT

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;