BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
luch25
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star
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;

View solution in original post

4 REPLIES 4
Astounding
PROC Star
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
Obsidian | Level 7

@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
Tourmaline | Level 20

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
PROC Star

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.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1419 views
  • 8 likes
  • 4 in conversation