Help with the data

[pp2014]

I have a data in file1 as follows:

data file1;

input prd $3. id $2. terr $4. x y z ;

cards;

ABC 11 M123  0 1 5

ABC 11 M345  1 0 2

ABC 12 M123  0 2 3

data file2;

input prd $3. id $2. terr $4. x y z ;

cards;

XYZ 11 M123 2 0 3

XYZ 13 M123 1 0 3

I want output for the file1 and file2 as follow (basically I want all id and terr to be shown in both files only with 0 values if they originally don't exist) :

file1 will have the following data:

ABC 11 M123  0 1 5

ABC 11 M345  1 0 2

ABC 12 M123  0 2 3

ABC 13 M123  0 0 0

file2 will have the following data:

XYZ 11 M123   2 0 3

XYZ 11 M345   0 0 0

xyz  12 M123   0 0 0

XYZ 13 M123  1 0 3

[pp2014]

Any help???

[Astounding]

It's not 100% clear how many variables you actually have and what should be hard-coded, but here's an approach anyway.  If the data sets are not already sorted, start there:

proc sort data=file1;

by id terr;

run;

proc sort data=file2;

by id terr;

run;

Then combine:

data new_file1;

merge file1 (in=in1) file2 (keep=id terr);

by id terr;

if in1=0 then do;

   prd='ABC';

   x=0;

   y=0;

   z=0;

end;

run;

Then similarly:

data new_file2;

   merge file1 (keep=id terr) file2 (in=in2);

   by id terr;

   if in2=0 then do;

      prd='XYZ';

      x=0;

      y=0;

      z=0;

   end;

run;

[Ksharp]

data file1;
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
;
run;
 
data file2;
input prd $ id $ terr $ x y z ;
cards;
XYZ 11 M123 2 0 3
XYZ 13 M123 1 0 3
;
run;

data want1;
 merge file1 file2(keep=id terr);
 by id terr;
 length new_prd $ 40;
 retain new_prd ;
 new_prd=coalescec(new_prd ,prd);
 x=coalesce(x,0);
 y=coalesce(y,0);
 z=coalesce(z,0);
 drop prd;
run;

data want2;
 merge file2 file1(keep=id terr);
 by id terr;
 length new_prd $ 40;
 retain new_prd ;
 new_prd=coalescec(new_prd ,prd);
 x=coalesce(x,0);
 y=coalesce(y,0);
 z=coalesce(z,0);
 drop prd;
run;

[PGStats]

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;