SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

a problem in merging many to many tables

Reply
New Contributor
Posts: 2

a problem in merging many to many tables

 1.png2.png3.png

 

 


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.

PROC Star
Posts: 1,558

Re: a problem in merging many to many tables

Posted in reply to withpeople86

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

Super User
Posts: 9,868

Re: a problem in merging many to many tables

Posted in reply to withpeople86

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,293

Re: a problem in merging many to many tables

Posted in reply to withpeople86

@withpeople86 wrote:

 1.png2.png3.png

 

 


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;
Trusted Advisor
Posts: 1,309

Re: a problem in merging many to many tables

Posted in reply to withpeople86

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.

Ask a Question
Discussion stats
  • 4 replies
  • 191 views
  • 0 likes
  • 5 in conversation