BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

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

4 REPLIES 4
pp2014
Fluorite | Level 6

Any help???

Astounding
Opal | Level 21

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
Super User
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
Opal | Level 21

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 766 views
  • 1 like
  • 4 in conversation