Help with SQL join

#1
Hello, I have some three tables laid out in the following fashion:

Code:
Table 1
Obs MonthYear Y X1
1   1/2004    2 3
2   1/2004    5 9
3   2/2004    1 2
...

Table 2
Obs MonthYear Y X2
1   1/2004    2 5
2   1/2004    5 5
3   2/2004    1 3
...

Table 3
Obs MonthYear Y X3
1   1/2004    2 10
2   1/2004    5 65
3   2/2004    1 20
...
How can I use SQL to sum it on a month/year level so that it'll be like the following?

Code:
Table 4
Obs MonthYear Y X1 X2 X3
1   1/2004    7 12 10 75
2   2/2004    1 2  3  20
...
 
#2
Figured it out! If anyone has a less computationally intensive way please share.

Here's my solution.

Code:
proc sql;
	create table tmp4 as
		select c.*, d.* from
		(
			select a.*, b.* from
				(select y, sum(x1) as tmp1x1 from tmp1 group by y) a
			left join
				(select y, sum(x1) as tmp2x1 from tmp2 group by y) b
				on a.y = b.y
		) c

		left join 

		(select y, sum(x1) as tmp3x1 from tmp3 group by y) d
				on c.y = d.y;
quit;
 

kdt

New Member
#3
If you don't mind it not being SQL, proc summary handles this quite nicely

Code:
/*Append all data*/
data x ;
   set table1 table2 table3 ;
   run ;
proc summary data=x nway ;
   class obs monthyear ;
   var y x1 x2 x3 ;
   output out=y(drop= _:) sum=/autoname;
   run;quit;
 

noetsi

Fortran must die
#4
The real lesson here is to have a primary and foreign key in the tables. Then you can do fairly simple joins and select statements :p