Counting unique observations based on another variable

[katesmith]

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!

[Reeza]

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;