topic a problem in merging many to many tables in SAS Data Management

a problem in merging many to many tables

withpeople86 — Thu, 17 May 2018 00:19:57 GMT

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.

Re: a problem in merging many to many tables

novinosrin — Thu, 17 May 2018 02:16:34 GMT

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

Re: a problem in merging many to many tables

Kurt_Bremser — Thu, 17 May 2018 06:14:19 GMT

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

Re: a problem in merging many to many tables

ballardw — Thu, 17 May 2018 15:15:54 GMT

@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;

Re: a problem in merging many to many tables

mkeintz — Mon, 21 May 2018 03:33:36 GMT

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.