topic Re: How to count the frequency of a value by unique ID's in SAS Enterprise Guide

How to count the frequency of a value by unique ID's

SLK — Fri, 24 Mar 2017 09:07:01 GMT

Hi!

I have a dataset where I would like to count the number of times different values of a variable occurs but only once for every Id.

ID Variable

1 A

2 B

3 B

4 A

5 A

1 A

1 B

3 B

4 B

I want this as a result:

Count_A Count_B

3 4

If ID 1 have A twice I only want it to count as one.

Madansas7b — Fri, 24 Mar 2017 09:16:03 GMT

proc sql;

create table want as

select count(distinct id) as Count_A,

count(distinct Variable) as Count_B

from have;

quit;

SLK — Fri, 24 Mar 2017 09:57:18 GMT

Thanks! I didn't quite work.

I used the following code (as I also had to group by other variable)

proc sql;
create table want as
select
year, variable, count(distinct ID)
from data
group by
year, variable
;
quit;