Date:         Thu, 12 Mar 2009 16:05:10 -0400
Reply-To:     Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Subject:      Re: strange issue with merge

not sure about that. I did the following:

data table1;
  length var1 $10 var2 $5;
  var1 = "1234"; var2="abc"; other=1; output;
  var1 = "2345"; var2="xyz"; other=2; output;
  var1 = "3456"; var2="abx"; other=3; output;
run;

data table2;
  length var1 $9 var2 $4;
  var1 = "1234xx"; var2="abcxx"; other=111; output;
  var1 = "2345"; var2="xyz"; other=222; output;
  var1 = "3456yy"; var2="abxyy"; other=333; output;
run;

proc sort data=table1;
  by var1 var2;
run;

proc sort data=table2;
  by var1 var2;
run;


data table3;
merge table1(in=a)  table2(in=b);
by var1 var2;
in1=a;
in2=b;
if a;
run;


I get 3 records (all records are in table1!). One (the middle one) was
replaced by table2's other variable.
All like expected.

Also if there are no matchings - why 0 obs??? All records from table1 must
come!

Is there a message in log about ??? "not properly sorted" for example?

Gerhard









On Thu, 12 Mar 2009 15:37:19 -0400, Jie Li <jieli20@GMAIL.COM> wrote:

>sorry! I accidently hit send key and sent out an unfinished email.
>
>here is my issue:
>
> I had two tables with two vars in common and wanted to merge them by
these
>two vars. I could visually see that these two table had at least one
record
>in common in terms of these 2 vars. however, when I did the following, I
got
>0 observation in table3.
>
>data table3;
>merge table1(in=a)  table2(in=b);
>by var1 var2;
>if a;
>run;
>
>then I checked the length, format for each of the vars. They are exactly
the
>same. So I took the one that I could see visually out from these two table
>and did the following:
>
>data temp1;
>set table1;
>where var1='1234' and var2='abc';
>t=1;
>run;
>
> data temp2;
>set table2;
>where var1='1234' and var2='abc';
>t=1;
>run;
>
>data temp3;
>merge temp1 temp2(rename=(var1=var1_1 var2=var2_1));
>by t;
>check1=(var1=var1_1);
>check2=(var2=var2_1);
>run;
>
>the values for check1 and check2 are all 1 which means the values of the
two
>vars from the 2 tables are the same. But why did I get 0 obs?
>
>Then, I tried the following:
>
>proc sql;
>create table table3 as
>select a.*,
>          b.*
>from table1 a join table2 b
>on a.var1=b.var1 and a.var2=b.var2;
>quit;
>
>I got the obs in common.
>
>Does anybody here know why this happens? thank you in advance!
>
>regards,
>Jie
>
>
>
>
>
>
>
>On Thu, Mar 12, 2009 at 3:22 PM, Jie Li <jieli20@gmail.com> wrote:
>
>> Hi, all,
>>
>> I got an odd issue when trying to merge 2 tables and was hoping somebody
>> here could offer a help.
>>
>> I had two tables with two vars in common and
>>