Help using Base SAS procedures

Counting unique observations based on another variable

Reply
New Contributor
Posts: 3

Counting unique observations based on another variable

Thank you for taking the time to help out! I'd like to count the number of unique personal_ids that purchased something, by year. There are multiple purchases for personal_ids on separate lines. I just want to know how many people bought something (anything), each year.

 

Variables:

year

personal_id

purchase

 

Example of data:

Year   Personal_ID  Purchase

2012  123                corn

2012  123                peas

2012  126               corn

2013  129               corn

2013  129               carrots

2013  142                

2014  150               corn

2014  150               peas

2014  150               peas

 

 

Thanks!

Super User
Posts: 23,697

Re: Counting unique observations based on another variable

Posted in reply to katesmith

Use SQL and count distinct or a double proc freq.

 

https://github.com/statgeek/SAS-Tutorials/blob/master/count_distinct_by_group

 

/*This demonstrates how to count the number of unique occurences of a variable
across groups. It uses the SASHELP.CARS dataset which is available with any SAS installation.
The objective is to determine the number of unique car makers by origin/

Note: The SQL solution can be off if you have a large data set and these are not the only two ways to calculate distinct counts.
If you're dealing with a large data set other methods may be appropriate.*/

*Count distinct IDs;
proc sql;
create table distinct_sql as
select origin, count(distinct make) as n_make
from sashelp.cars
group by origin;
quit;

*Double PROC FREQ;
proc freq data=sashelp.cars noprint;
table origin * make / out=origin_make;
run;

proc freq data=origin_make noprint;
table origin / out= distinct_freq;
run;

title 'PROC FREQ';
proc print data=distinct_freq;
run;
title 'PROC SQL';
proc print data=distinct_sql;
run;
Ask a Question
Discussion stats
  • 1 reply
  • 249 views
  • 0 likes
  • 2 in conversation