Distinct variable

[PetePatel]

Hi there,

I have a data set which contains several ID's such as this:

ID	Subject
1	High
1	Med
1	Med
2	Low
2	Low
3	High
3	Low
3	Med
3	Med

I would like to count all distinct values within the subject variable and arrive at this:

ID	Distinct
1	2
2	1
3	3

Is there a data step solution I can use to achieve this?

Cheers,

Pete

[art297]

Depends! If your data are already grouped as in your example, then the following would work:

data have;
  input ID  Subject $;
  cards;
1   High
1   Med
1   Med
2   Low
2   Low
3   High
3   Low
3   Med
3   Med
;

data want (drop=Subject);
  set have;
  by ID Subject notsorted;
  if first.id then distinct=0;
  if first.Subject then distinct+1;
  if last.id then output;
run;

Art, CEO, AnalystFinder.com

[novinosrin]

data have;
  input ID  Subject $;
  cards;
1   High
1   Med
1   Med
2   Low
2   Low
3   High
3   Low
3   Med
3   Med
;

proc sql;
create table want as
select id, count(distinct subject) as distinct
from have
group by id;
quit;