CODEWITHSUNDEEP
sas
Angus
Angus

Reputation: 167

Unusual 1 to many SAS merge

Given the below tables I'd like to get the Want output. Any ideas please? For each row of grp from table one I need to end up with all 3 rows that match grp from table two

data one;
   accno=123; grp=101; output;
   accno=234; grp=101; output;
run;

data two;
   grp=101; inc=123; exc=.; desc='abc'; output;
   grp=101; inc=.; exc=123; desc='def'; output;
   grp=101; inc=.; exc=.; desc='ghi'; output;
run;

/*
Want:
accno agentgrp inc exc desc
----- -------- --- --- ----
123   101      123 .   abc
123   101      .   123 def
123   101      .   .   ghi
234   101      123 .   abc
234   101      .   123 def
234   101      .   .   ghi
*/

Upvotes: 0

Views: 45

Answers (2)

Richard
Richard

Reputation: 27508

You can do a left outer join in DATA Step by looping over table two with the point= option.

data want (keep=accno agentgrp inc exc desc) ;
  set one ;
  do _n_ = 1 to nobs ;
    set two (rename=(grp=agentgrp)) nobs=nobs point=_n_ ;
    if agentgrp = grp then output ;
  end ;
run ;

Upvotes: 1

Angus
Angus

Reputation: 167

proc sql;
   create table three as
   select a.*,b.*
   from one a
   full outer join two b
   on a.grp=b.grp;
quit;

Upvotes: 3

Related Questions