topic Re: how to do this in SAS Procedures

how to do this

Mike_Davis — Fri, 04 May 2012 18:05:11 GMT

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 B 2 1

2 A 3 1

2 B 3 2

2 C 3 4

Re: how to do this

Haikuo — Fri, 04 May 2012 18:17:40 GMT

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

Re: how to do this

Haikuo — Fri, 04 May 2012 18:43:19 GMT

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 B

2 A

2 B

2 C

;

data want;

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

do until (last.type);

set have;

by id type;

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;

run;

proc print;run;

Regards,

Haikuo

Re: how to do this

Ksharp — Mon, 07 May 2012 04:23:56 GMT

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