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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.