I have the input
ID | Class |
0 | A |
0 | A |
0 | B |
1 | A |
1 | C |
1 | C |
2 | B |
2 | B |
2 | C |
What I want is to extract distinct class values in each ID group by, the output will be something like this:
ID | Class A | Class B | Class C |
0 | 2 | 1 | 0 |
1 | 1 | 0 | 2 |
2 | 0 | 2 | 1 |
My thought is to use count distinct inside group by something like:
select ID,
sum(if(Class='A', 1, 0) as Class_A,
sum(if(Class='B', 1,0) as Class_B,
sum(if(Class='C', 1, 0) as Class_C
from table
group by ID
But then the problem is that it's inefficiency if we have 100 classes. Any idea we can auto-extract count of distinct classes in group by?
Here you are:
data have;
input ID Class $;
cards;
0 A
0 A
0 B
1 A
1 C
1 C
2 B
2 B
2 C
;
run;
proc freq data=have;
tables ID * Class / sparse out=count_ID_CLASS(drop=PERCENT);
run;
proc transpose data=count_ID_CLASS out=want;
by id;
id class;
run;
/* end of program */
Koen
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!
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.