Counting unique observations based on another variable

katesmith — Mon, 04 Dec 2017 17:28:49 GMT

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

Thanks!

Re: Counting unique observations based on another variable

Reeza — Mon, 04 Dec 2017 17:34:05 GMT

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;

topic Re: Counting unique observations based on another variable in SAS Procedures

Counting unique observations based on another variable

Re: Counting unique observations based on another variable