Help using Base SAS procedures

how to do this

Reply
Regular Contributor
Posts: 222

how to do this

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

Respected Advisor
Posts: 3,156

Re: how to do this

Posted in reply to Mike_Davis

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

Respected Advisor
Posts: 3,156

Re: how to do this

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

Super User
Posts: 10,018

Re: how to do this

Posted in reply to Mike_Davis

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

Ask a Question
Discussion stats
  • 3 replies
  • 154 views
  • 6 likes
  • 3 in conversation