how to do this

[Mike_Davis]

Hello ,

Here is a data set have,

The problem is I want new variable count_sub count the number of each type for each id,

Please help.

Thanks!

data have;
input id type $;
cards;
1 A
1 A
1 A
1 B
2 A
2 B
2 B
2 C
2 C
2 C
2 C
;

proc sql noprint;
create table want_bad as
select *,count(distinct type) as count_type,count(type) as count_sub
from have
group by id
order by id,type
;
quit;

The dataset want should be like this:

1 A 2 3

1 A 2 3

1 A 2 3

1 B 2 1

2 A 3 1

2 B 3 2

2 B 3 2

2 C 3 4

2 C 3 4

2 C 3 4

2 C 3 4

[Haikuo]

Since you will need two different grouping:

proc sql noprint;

create table want_bad as

select a.*, b.count_sub from

(select *,count(distinct type) as count_type

from have

group by id) a

left join

(select *, count(type) as count_sub

from have

group by id ,type) b

on a.id=b.id and a.type=b.type

order by id,type

;

quit;

Regards,

Haikuo

[Haikuo]

Apparently it can be done more intuitive by using SQL, just in case you are wondering how it can be done using data step, here is one approach:

data have;

input id type $;

cards;

1 A

1 A

1 A

1 B

2 A

2 B

2 B

2 C

2 C

2 C

2 C

;

data want;

do count_type=1 by 1 until (last.id);

  do until (last.type);

  set have;

  by id type;

  end;

end;

do until (last.id);

  do count_sub=1 by 1 until (last.type);

  set have;

  by id type;

  end;

  do until (last.type);

  set have;

  by id type;

  output;

  end;

end;

run;

proc print;run;

Regards,

Haikuo

[Ksharp]

How about:

data have;
input id type $;
cards;
1 A
1 A
1 A
1 B
2 A
2 B
2 B
2 C
2 C
2 C
2 C
;
run;
proc sql;
create table want as
 select id,type,id_sum,type_sum 
  from (select * from have left join (select id as _id,type as _type,count(*) as type_sum from have group by id,type) on id=_id and type=_type )
        left join (select id as __id,count(distinct type) as id_sum from have group by id) on id=__id ;
quit;

Ksharp