You can get the final table by following proc sql few steps. 1. Get the cartesian Product of just Id and event. 2. Merge the step1 result with the original id_all table and get the common rows. 3. Get the subset from step1 which is excluded from step2 and add it back data event;
input event $10.;
datalines;
Month1
Month2
Month3
run;
data id_all;
infile datalines missover;
input ID event :$10. value value2;
datalines;
5656 Month1 13 324
6565 Month2 31 465
4663 Month1 14 697
4242
1234 Month3 14 656
5421 Month1 12 647
run;
proc sql;
create table subset
as
select a.id, b.event
from id_all a,event b;
quit;
proc sql;
create table match
as
select a.id, b.event, a.value, a.value2
from id_all a
left join subset b
on a.id=b.id
and a.event=b.event
;
quit;
proc sql;
create table all
as
select * from match
union
(select id, event, . as value, . as value2 from subset
except
select id, event, . as value, . as value2 from match);
quit;
proc sort data=all; by id, event; run;
proc print data=all; run;
... View more
