Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Distinct Count

Reply
Regular Contributor
Posts: 181

Distinct Count

[ Edited ]

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.

Super Contributor
Posts: 251

Re: Distinct Count

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;

 

Ask a Question
Discussion stats
  • 1 reply
  • 198 views
  • 0 likes
  • 2 in conversation