On Tue, 24 Jul 2007 22:33:25 -0700, Night_listener <ytsteven@GMAIL.COM>
wrote:

>Hi, everyone
>
>I have a question on 1-N merging. Suppose we have 2 datasets
>
>data1:
>
>1 A aa
>2 C cc
>3 E ee
>
>data2:
>
>1  A aaa
>2  A aaaa
>3  A aaaaa
>4  B bbb
>5  B bbbb
>6  B bbbbb
>7  C ccc
>8  C cccc
>9  D ddd
>10 D dddd
>11 E eee
>12 E eeee
>
>I want this output;
>
>1 A aa aaa
>2 A aa aaaa
>3 A aa aaaaa
>4 C cc ccc
>5 C cc cccc
>6 E ee eee
>7 E ee eeee
>
>Is there any easy way to do it, other than combining them together and
>delete ods Bs and Ds?
>
>Many thanks!
>
>Steven

Hi, it's just a standard sql join (natural/ inner)

data data1;
input var1 $ var2 $;
cards;
A aa
C cc
E ee
;

data data2;
input var1 $ var3 $;
cards;
A aaa
A aaaa
A aaaaa
B bbb
B bbbb
B bbbbb
C ccc
C cccc
D ddd
D dddd
E eee
E eeee
;

proc sql;
 select var1, var2, var3
 from data1 natural join data2;
quit;


var1      var2      var3
---------------------------
A         aa        aaa
A         aa        aaaa
A         aa        aaaaa
C         cc        ccc
C         cc        cccc
E         ee        eee
E         ee        eeee

OR

data data3;
 merge data1 (in=a) data2;
 by var1;
 if a then output;;
run;

On Jul 25, 4:21 pm, s...@KLP.NO (Arild S) wrote:
> On Tue, 24 Jul 2007 22:33:25 -0700, Night_listener <ytste...@GMAIL.COM>
> wrote:
>
>
>
>
>
> >Hi, everyone
>
> >I have a question on 1-N merging. Suppose we have 2 datasets
>
> >data1:
>
> >1 A aa
> >2 C cc
> >3 E ee
>
> >data2:
>
> >1  A aaa
> >2  A aaaa
> >3  A aaaaa
> >4  B bbb
> >5  B bbbb
> >6  B bbbbb
> >7  C ccc
> >8  C cccc
> >9  D ddd
> >10 D dddd
> >11 E eee
> >12 E eeee
>
> >I want this output;
>
> >1 A aa aaa
> >2 A aa aaaa
> >3 A aa aaaaa
> >4 C cc ccc
> >5 C cc cccc
> >6 E ee eee
> >7 E ee eeee
>
> >Is there any easy way to do it, other than combining them together and
> >delete ods Bs and Ds?
>
> >Many thanks!
>
> >Steven
>
> Hi, it's just a standard sql join (natural/ inner)
>
> data data1;
> input var1 $ var2 $;
> cards;
> A aa
> C cc
> E ee
> ;
>
> data data2;
> input var1 $ var3 $;
> cards;
> A aaa
> A aaaa
> A aaaaa
> B bbb
> B bbbb
> B bbbbb
> C ccc
> C cccc
> D ddd
> D dddd
> E eee
> E eeee
> ;
>
> proc sql;
>  select var1, var2, var3
>  from data1 natural join data2;
> quit;
>
> var1      var2      var3
> ---------------------------
> A         aa        aaa
> A         aa        aaaa
> A         aa        aaaaa
> C         cc        ccc
> C         cc        cccc
> E         ee        eee
> E         ee        eeee
>
> OR
>
> data data3;
>  merge data1 (in=a) data2;
>  by var1;
>  if a then output;;
> run;- Hide quoted text -
>
> - Show quoted text -

Thank you, sudha and Arild. Your codes work.

sudha, I think we can simplify the condition "if c=0 or a=1". It
equals "if a" as in Arild's code.

Thank you guys again.

Steven