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

Solved
Occasional Contributor
Posts: 13

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

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.

Accepted Solutions
Solution
‎03-24-2017 05:57 AM
Occasional Contributor
Posts: 13

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

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;

All Replies
Occasional Contributor
Posts: 12

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

proc sql;

create table want as

select count(distinct id) as Count_A,

count(distinct  Variable) as Count_B

from have;

quit;

Solution
‎03-24-2017 05:57 AM
Occasional Contributor
Posts: 13

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

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;

☑ This topic is solved.