Vertical joining

[asinusdk]

I wonder how I can vertically join two tables based on the information of one table.

So, here are two tables. 

data1
year	id	v1	v2
2002	1	3	3
2003	1	1	2
2002	2	1	2
2003	2	1	3
data2
year	id	v1	v2
2004	2	1	3
2005	2	1	2
2006	2	2	4
2004	3	2	4
2005	3	1	2
2006	3	4	2

I only want additional information from data 2 about id 1 and 2 who are in data1. Thus, the expected outcome is below. could you please help me? thanks so much..

year	id	v1	v2
2002	1	3	3
2003	1	1	2
2002	2	1	2
2003	2	1	3
2004	2	1	3
2005	2	1	2
2006	2	2	4

I tried following codes. 

proc sql;
create table clean as select *
from data1 except select * from data2  group by year, id; quit;

but it doesn't produce what I want..

[Astounding]

A simple approach:

data want;

set data1 (in=in_data1) data2;

by id year;

if first.id then keepme = in_data1;

retain keepme;

if keepme;

run;

Your incoming data sets both need to be sorted by ID YEAR for this to work.

Also, this approach relies on DATA1 containing data for the earliest year, and DATA2 containing data for later years.

[asinusdk]

Thank you very much. It helps me to figure out 🙂 

[novinosrin · Accepted solution]

data one;
input year	id	v1	v2;
cards;
2002	1	3	3
2003	1	1	2
2002	2	1	2
2003	2	1	3
;

data two;
input year	id	v1	v2;
cards;
2004	2	1	3
2005	2	1	2
2006	2	2	4
2004	3	2	4
2005	3	1	2
2006	3	4	2
;
proc sql;
create table want as

   (select * from one)
   union
   (select * from two
   where id in (select id from one)
         )
order by id;
quit;