topic Re: Count of unique values output as new variable in SAS Programming

Count of unique values output as new variable

luch25 — Tue, 15 Feb 2022 05:58:24 GMT

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!!

Re: Count of unique values output as new variable

Astounding — Tue, 15 Feb 2022 06:59:16 GMT

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;

Re: Count of unique values output as new variable

PeterClemmensen — Tue, 15 Feb 2022 07:39:04 GMT

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

Re: Count of unique values output as new variable

Amir — Tue, 15 Feb 2022 08:02:52 GMT

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.

Re: Count of unique values output as new variable

luch25 — Tue, 15 Feb 2022 13:33:59 GMT

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