help with merging the dataset

[shalmali]

Dear All,

I need help in merging two datasets: File A and File B. File A has the following information:

Year Name1 Name2

2000     A     X

2000     B     Y

2001     C     Z

2002     D     T

File B has the following information:

Year Name National     Local

2000     A     0.10         0.36

2000    B     0.11         0.36

2000     C     0.14         0.33

2000     X     0.17         0.23

2001     U     0.12         0.39

2001     Y    0.18         0.33

2001    Z     0.21         0.34

2002     D     0.12        0.35

2002    V     0.31         0.43

2003     E     0.13         0.33

2003     F     0.13         0.36

2003     G     0.21         0.43

2003     W     0.14         0.13

Desired Output:

Year Name1 Name2     Name1_National     Name1_local   Name2_National     Name2_local

2000     A     X               0.1                         0.36               0.17                         0.23

2000     B     Y             0.11                         0.36               .                               .              

2001     C     Z              .                                 .                0.21                         0.34

2002     D     T             0.12                         0.35               .                              .

Can someone please share code with me so that I could get this desired output?

Thank you in advance.

[pobel]

I think two MERGE step can get what you want:

data a;

  input Year Name1 $ Name2 $;

  cards;

2000     A     X

2000     B     Y

2001     C     Z

2002     D     T

;

data b;

   input Year Name $ National     Local;

   cards;

2000     A     0.10         0.36

2000    B     0.11         0.36

2000     C     0.14         0.33

2000     X     0.17         0.23

2001     U     0.12         0.39

2001     Y    0.18         0.33

2001    Z     0.21         0.34

2002     D     0.12        0.35

2002    V     0.31         0.43

2003     E     0.13         0.33

2003     F     0.13         0.36

2003     G     0.21         0.43

2003     W     0.14         0.13

;

data c_1;

    merge a(in=inleft) b(rename=(name=name1 national=name1_national local=name1_local));

  by year name1;

  if inleft;

run;

data c_2;

    merge c_1(in=inleft) b(rename=(name=name2 national=name2_national local=name2_local));

  by year name2;

  if inleft;

run;

[Haikuo]

Here is a Hash() approach:

data h1;

input  (Year Name1 Name2) ($);

cards;

2000     A     X

2000     B     Y

2001     C     Z

2002     D     T

;

data h2;

input (Year Name) ($) National     Local;

cards;

2000     A     0.10         0.36

2000    B     0.11         0.36

2000     C     0.14         0.33

2000     X     0.17         0.23

2001     U     0.12         0.39

2001     Y    0.18         0.33

2001    Z     0.21         0.34

2002     D     0.12        0.35

2002    V     0.31         0.43

2003     E     0.13         0.33

2003     F     0.13         0.36

2003     G     0.21         0.43

2003     W     0.14         0.13

;

data want;

  if _n_=1 then do;

    if 0 then set h2(rename=(national=_n local=_l));

     declare hash h(dataset:'h2(rename=(national=_n local=_l))');

     h.definekey('year','name');

     h.definedata('_n','_l');

     h.definedone();

  end;

  set h1;

    if h.find(key:year, key:name1)=0 then do;name1_national=_n; name1_local=_l;end;

    if h.find(key:year,key:name2)=0 then do; name2_national=_n; name2_local=_l;end;

    drop _: name;

run;

proc print;run;

Haikuo

[shalmali]

Thank you Haikuo and pobel for your quick responses. your comments are very helpful.