One more!
data file1;
length prd $3 id $2 terr $4;
input prd id terr x y z ;
cards;
ABC 11 M123 0 1 5
ABC 11 M345 1 0 2
ABC 12 M123 0 2 3
;
data file2;
length prd $3 id $2 terr $4;
input prd id terr x y z ;
cards;
XYZ 11 M123 2 0 3
XYZ 13 M123 1 0 3
;
proc sql;
create table new_file1 as
select a.prd, b.id, b.terr,
coalesce(x, 0) as x,
coalesce(y, 0) as y,
coalesce(z, 0) as z
from
((select unique prd from file1) as a
cross join
( select id, terr from file1
union
select id, terr from file2 ) as b)
left join file1 as c on b.id=c.id and b.terr=c.terr;
create table new_file2 as
select a.prd, b.id, b.terr,
coalesce(x, 0) as x,
coalesce(y, 0) as y,
coalesce(z, 0) as z
from
((select unique prd from file2) as a
cross join
( select id, terr from file2
union
select id, terr from file1 ) as b)
left join file2 as c on b.id=c.id and b.terr=c.terr;
quit;
... View more
