a problem in merging many to many tables

[withpeople86]

 

I would like to make the third table using the first two tables. 

I thought of the following syntax:

proc sql; create table t1 as select *

from t3 a left join t2 b

on a.year=b.year and a.id=b.id;quit;

However, it only showed the first observation each year from the table 1.

Please help me to figure out.

[novinosrin]

pics or screen shots doesn't help. Please post the values as text

[Kurt_Bremser]

See my footnotes for how to post example data and code.

[ballardw]

---

@withpeople86 wrote:

 

I would like to make the third table using the first two tables. 

I thought of the following syntax:

proc sql; create table t1 as select *

from t3 a left join t2 b

on a.year=b.year and a.id=b.id;quit;

However, it only showed the first observation each year from the table 1.

Please help me to figure out.

---

Which table is t3? which is t2?

What formats are currently involved with the variables? When you compare or join with = the values have to actually be equal. Numeric variables with decimals portions suppressed by "look" the same when examining tables but not be equal. If the variables are text you run into potential issues with leading characters.

did you try

proc sql; 
   create table t1 as 
   select a.*,b.var2,b.var3
   from t3 a left join t2 b
   on a.year=b.year and a.id=b.id;
quit;

[mkeintz]

Your table T! has one record with YEAR=3, ID=4.   And T2 has two such records, so T3 should also have two such records, but you posting shows only one.  If that  is an error, than this program takes advantage of the fact that both T1 and T2 are sorted by ID/YEAR:

data t1;
  input year id var1 :$1.;
datalines;
1 1 a
2 1 b
3 1 a
4 1 b
1 2 b
2 2 b
3 2 a
4 2 b
1 3 b
2 3 b
3 3 b
4 3 c
1 4 c
2 4 a
3 4 b
4 4 a
run;
data t2;
  input year id var2 var3;
datalines;
1 1 1 1
1 1 1 2
1 1 1 13
2 1 1 3
1 2 0 14
1 2 0 5
3 2 0 5
2 3 1 2
2 3 1 3
4 3 1 5
3 4 1 3
3 4 0 5
4 4 0 2
4 4 1 1
run;
data want;
  merge t1 t2;
  by id year;
run;

This program works because T1 has only 1 record for each YEAR/ID combination.