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
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.