Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Occasional Contributor SLK
Occasional Contributor
Posts: 13
Accepted Solution

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 SLK
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;

View solution in original post


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 SLK
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 208 views
  • 0 likes
  • 2 in conversation