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
Any help???
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.