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
PROC Star

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.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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