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.

Highlighted
PROC Star
Posts: 1,284

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,548

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: 12,994

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;
Ask a Question
Discussion stats
  • 3 replies
  • 113 views
  • 0 likes
  • 4 in conversation