topic Re: Help with the data in SAS Procedures

Help with the data

pp2014 — Wed, 30 Mar 2016 15:31:15 GMT

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

Re: Help with the data

pp2014 — Wed, 30 Mar 2016 16:12:12 GMT

Any help???

Re: Help with the data

Astounding — Wed, 30 Mar 2016 16:20:29 GMT

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;

Re: Help with the data

Ksharp — Thu, 31 Mar 2016 02:25:01 GMT

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;

Re: Help with the data

PGStats — Thu, 31 Mar 2016 02:30:36 GMT

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;