topic Merger question in SAS Programming

Merger question

aidant01 — Sun, 02 Dec 2018 06:04:33 GMT

The code I tried is:

data mergeddata;

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

by country;

run;

Re: Merger question

novinosrin — Sun, 02 Dec 2018 06:08:18 GMT

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

Re: Merger question

novinosrin — Sun, 02 Dec 2018 06:18:36 GMT


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;

Re: Merger question

Reeza — Sun, 02 Dec 2018 07:22:16 GMT

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.

Re: Merger question

novinosrin — Sun, 02 Dec 2018 07:43:23 GMT

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;