Count of unique values output as new variable

[luch25]

Hi all,

I have a dataset where I need to count the number of unique values of the variable "class" for each "studyid". I know that I can do this within proc sql, but I'm getting stuck because I need to output the counts of unique classes by studyid as a new column in the original dataset structure. So for the dataset:

data have;
input id class;
cards;
1 1
1 2
1 3
1 3
1 4
1 5
1 5
1 5
2 1
2 2
2 2
2 3
2 4
2 4
;

I would want the resulting dataset to look like this: 

id	class	count
1	1	1
1	2	1
1	3	2
1	3	2
1	4	1
1	5	3
1	5	3
1	5	3
2	1	1
2	2	2
2	2	2
2	3	1
2	4	2
2	4	2

Reasoning for the "count" variable:

• id=1 has a single value of class=1, so count=1
• id=1 has a single value of class=2, so count=1
• id=1 has two values of class=3, so count=2 for both of the rows with id=1 and class=3
• id=1 has three values of class=5, so count=3 for all the rows with id=1 and class=5
• etc

Any guidance would be greatly appreciated!!

[Astounding · Accepted solution]

Try a double loop, first to count and second to output:

data want;
count = 0;
do until (last.class);
set have;
by ID class;
count + 1;
end;
do until (last.class);
set have;
by id class;
output;
end;
run;

[luch25]

@Astounding and @PeterClemmensen THANK YOU. Both of these solutions work perfectly and are quite clever. Greatly appreciate your help- love learning from this community. 

[PeterClemmensen]

An SQL alternative. Though, I like @Astounding double DoW approach better 🙂

data have;
input id class;
cards;
1 1
1 2
1 3
1 3
1 4
1 5
1 5
1 5
2 1
2 2
2 2
2 3
2 4
2 4
;

proc sql;
   create table want as
   select id
        , class
		, (select count(class) 
           from have a 
           where a.id = b.id and a.class = b.class) as count
   from have b
   ;
quit;

Result:

id  class  count 
1   1      1 
1   2      1 
1   3      2 
1   3      2 
1   4      1 
1   5      3 
1   5      3 
1   5      3 
2   1      1 
2   2      2 
2   2      2 
2   3      1 
2   4      2 
2   4      2 

[Amir]

Hi @luch25,

As I was reading your question, I also thought of the double DOW-loop as a solution.

But I mainly wanted to commend you on the way you asked your question, from the data step with datalines for input, the output for the given input, and, as importantly, your reasoning showing the rules behind how you want the output to be achieved.

Let this be a good lesson to others asking questions, as evidenced by the respondents who were able to reply with solutions and not a single follow up question.

Well done.

Kind regards,

Amir.