topic help with merging the dataset in SAS Programming

help with merging the dataset

shalmali — Thu, 15 Nov 2012 00:41:07 GMT

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.

Re: help with merging the dataset

pobel — Thu, 15 Nov 2012 01:06:08 GMT

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;

Re: help with merging the dataset

Haikuo — Thu, 15 Nov 2012 02:47:21 GMT

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

Re: help with merging the dataset

shalmali — Thu, 15 Nov 2012 22:29:01 GMT

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