Extract distinct values inside group by

vietlinh12hoa — Mon, 17 May 2021 08:16:53 GMT

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?

Re: Extract distinct values inside group by

sbxkoenk — Mon, 17 May 2021 09:02:24 GMT

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

topic Extract distinct values inside group by in SAS Programming

Extract distinct values inside group by

Re: Extract distinct values inside group by