Merger question

[aidant01]

The code I tried is:

data  mergeddata;

merge datasetA (in=in1) datasetB (in=in2);

by country;

run;

Screen Shot 2018-12-02 at 1.00.51 PM.png

[novinosrin]

You need a join based on value and not a merge based on position. So switch to proc sql join, you will be fine. 

[novinosrin]

data a;
input airline & $20. country  :$15.;
cards;
Air Asia     Vietnam
Vietjet      Vietnam
;

data b;
input country  $10. city & :$15.;
cards;
Vietnam   Hanoi
Vietnam   Ho Chi minh
;

proc sql;
create table want as
select a.*, city
from 
a x1,b x2
where x1.country=x2.country;
quit;

[Reeza]

This is a many to many join. SAS data step does not do a many to many join, but a SQL join can do this instead. A hash solution may also work, but probably not worth the effort.

[novinosrin]

Very good idea of using Hash

data a;
input airline & $20. country  :$10.;
cards;
Air Asia     Vietnam
Vietjet      Vietnam
;

data b;
input country  $10. city & :$15.;
cards;
Vietnam   Hanoi
Vietnam   Ho Chi minh
;
data want;
set a;
by country;	/*sort not required but improves performance*/
if _n_=1 then do;
if 0 then set b;
  declare hash H (dataset:'b',multidata:'y') ;
   h.definekey  ("country") ;
   h.definedata ("city") ;
   h.definedone () ;
end;
do rc=h.find() by 0 while(rc=0);
output;
rc=h.find_next();
end;
drop rc;
run;