Jie,

You never did provide the sample data that Gerhard had suggested you provide but, since you indicated that you got what you were expecting using compress, I think your problem is leading blanks that can be corrected with the left function.

For example, if you create the following two datasets:

data table1; length var1 $3.; length var2 $3.; var1="x "; var2="y "; t=1; output; var1="y "; var2="y "; t=2; output; var1="x "; var2="x "; t=3; output; run;

data table2; length var1 $3.; length var2 $3.; var1=" x"; var2=" y"; v=1; output; var1=" y"; var2=" y"; v=2; output; var1=" x"; var2=" x"; v=3; output; run;

Your original code won't produce any records. However, with the following modifications, it will do what you expect:

data table1; set table1; var1=left(var1); var2=left(var2); run;

data table2; set table2; var1=left(var1); var2=left(var2); 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; if a and b; run;

Could that be what your problem is?

Art --------- On Thu, 12 Mar 2009 16:44:53 -0400, Jie Li <jieli20@GMAIL.COM> wrote:

>Hi, Akshaya > >I tried full join. The # of records I got is # of records of table1+# of >records of table2. thanks! > >regards, >Jie > >On Thu, Mar 12, 2009 at 4:27 PM, Akshaya <akshaya.nathilvar@gmail.com>wrote: > >> >> Just wondering, are there any records left if you do a full join? >> data table3; >> merge table1(in=a) table2(in=b); >> by var1 var2; >> if a or b; >> run; >> >> >> On Thu, Mar 12, 2009 at 4:20 PM, Jie Li <jieli20@gmail.com> wrote: >> >>> sorry, it should be: >>> data table3; >>> merge table1(in=a) table2(in=b); >>> by var1 var2; >>> if a and b; >>> run; >>> >>> there was no error message and no warnings, etc. just saying there is 0 >>> obs >>> in table3. >>> >>> >>> I also tried the following and got obs in table3: >>> >>> data table1; >>> set table1; >>> key=compress(var1||var2); >>> run; >>> >>> data table2; >>> set table2; >>> key=compress(var1||var2); >>> run; >>> >>> proc sort data=table1; by key;run; >>> proc sort data=table2; by key;run; >>> >>> data table3; >>> merge table1(in=a) table2(in=b); >>> by key; >>> if a and b; >>> run; >>> >>> >>> Regards, >>> Jie >>> >>> >>> On Thu, Mar 12, 2009 at 4:09 PM, Gerhard Hellriegel < >>> gerhard.hellriegel@t-online.de> wrote: >>> >>> > I also changed the length definitions in table1 to something shorter >>> than >>> > table2 and got a message about "possible strange results", but all the >>> > same - nothing "strange". >>> > >>> > >>> > >>> > On Thu, 12 Mar 2009 16:05:10 -0400, Gerhard Hellriegel >>> > <gerhard.hellriegel@T-ONLINE.DE> wrote: >>> > >>> > >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 >>> > >>> >>> > >>> >> >> >> >> -- >> AkshayA! >>