Distinct Count

[Ujjawal]

I want to calculate distinct count by group. When i tried to create aggregate measure and use distinct count, it returns an error as variable is character (see the variable 'code' below)

ID	flag	Code
1	0	B1
1	0	B1
1	0	B2
1	1	A1
1	1	A1
1	1	A1
2	0	D1
2	0	D1
2	1	C1
2	1	C1

I want to generate the following output in cross tab. For ID =1, it should return this output (i.e. distinct count of variable 'Code'.

flag	Status
0	2
1	1

I would put ID in the drop down.

[KachiM]

Do you want a data step program?

You may presort the dataset by ID and flag. I used the grouped data by using NOTSORTED option in BY statement.

data want;
   do until(last.flag);
      set have;
      by id flag notsorted;
      if first.flag then do; pre_Code = Code; count = 1; end;
      else if pre_Code ^= Code then count + 1;
   end;
keep id flag count;
run;