data test;
input
subject level;
cards;
1001 2
1001 3
1001 3
1001 2
1002 1
1002 3
1003 4
1003 4
1003 4
;
run;
proc sql;
create table want as
select test.*, count_max from test left join
(
select distinct subject, count(subject_level) as count_max
from (select test.*, catx('_',subject,level) as subject_level from test)
where subject_level in (select catx('_',subject,max(level)) as subject_level
from test group by subject)
group by subject_level
) as temp
on test.subject=temp.subject;
quit;
proc print;run;
|