Below one way that should work.
data have;
input id 1 hp_01 $ 3-5 hp_02 $ 7-9 hp_03 $ 11-13 hp_04 $ 15-17 hp_05 $ 19-21
hp_06 $ 23-25 hp_07 $ 27-29 hp_08 $ 31-33 hp_09 $ 35-37 hp_10 $ 39-41
hp_11 $ 43-45 hp_12 $ 47-49;
datalines;
1 MCO MCO MCO PPO PPO PPO PPO PPO PPO PPO PPO PPO
2 PDP PDP PDP PDP PDP PDP PDP PDP PDP PDP MCO MCO
3 EDP EDP EDP EDP EDP EDP PPO PPO PPO PPO PPO PPO
4 MCO MCO MCO MCO MCO MCO MCO MCO MCO EDP EDP EDP
5 EDP EDP EDP EDP MCO MCO PPO PPO PPO PPO EDP EDP
6 MCO NET NET NET EDP EDP NET NET MCO MCO MCO MCO
7 PPO PPO PPO NET NET NET NET NET PPO PPO MCO MCO
8 EDP MCO MCO MCO PPO PPO PPO PPO PPO NET NET NET
9 NET NET NET NET PPO PPO PPO PPO PPO PPO PPO PPO
;
data long;
set have;
array hp {*} hp_01 - hp_12;
do _i=1 to dim(hp);
month_num=input(scan(vname(hp[_i]),-1,'_'),best32.);
health_plan=hp[_i];
output;
end;
keep id month_num health_plan;
run;
proc sql;
create table counts as
select
id
,health_plan
,max(month_num) as max_month_num
,count(*) as n_occurences
from long
group by id, health_plan
order by id, n_occurences, max_month_num
;
quit;
data want;
set counts;
by id;
if last.id;
run;
proc print data=want;
run;
... View more
