Fluorite | Level 6

## Help with the data

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&colon;

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&colon;

XYZ 11 M123   2 0 3

XYZ 11 M345   0 0 0

xyz  12 M123   0 0 0

XYZ 13 M123  1 0 3

4 REPLIES 4
Fluorite | Level 6

Any help???

Opal | Level 21

## Re: Help with the data

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;

Super User

## Re: Help with the data

```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;

```
Opal | Level 21

## Re: Help with the data

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;
``````
PG
Discussion stats
• 4 replies
• 766 views
• 1 like
• 4 in conversation