Merge many to one

[Elena]

Hi,

I have this 2 dataset

data D1;
input PROV$ mes sub$ ;
datalines;
001002 1 B
001002 2 B
001003 1 B
001003 2 B
001004 1 B
001004 2 B
;
run;
data D2;
input PROV$ sub$ ;
datalines;
001002 B
001003 A
001004 A

;
run;

I want

PROV	mes	sub
001002	1	B
001002	2	B
001003	1	A
001003	2	A
001004	1	A
001004	2	A

The merge

data d3;
merge d1 d2;
by prov;
run;

produces

PROV	mes	sub
001002	1	B
001002	2	B
001003	1	A
001003	2	B
001004	1	A
001004	2	B

that I don't want.

Thank.

[Patrick]

Below two data step options - with slightly different outcomes so use the one which returns what you need.

data D1;
  input PROV$ mes sub$;
  datalines;
001002 1 B
001002 2 B
001003 1 B
001003 2 B
001004 1 B
001004 2 B
001099 2 X
;
run;

data D2;
  input PROV$ sub$;
  datalines;
001002 B
001003 A
001004 A
;
run;

data d3;
  merge d1(drop=sub) d2;
  by prov;
run;

data want;
  set d1;
  if _n_=1 then
    do;
      dcl hash h1(dataset:'d2');
      h1.defineKey('prov');
      h1.defineData('sub');
      h1.defineDone();
    end;
  _rc=h1.find();
  drop _rc;
run;

proc print data=d3;
run;
proc print data=want;
run;