topic Re: Vertical joining in SAS Programming

Vertical joining

asinusdk — Tue, 16 Oct 2018 22:32:13 GMT

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..

Re: Vertical joining

Astounding — Tue, 16 Oct 2018 22:35:24 GMT

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.

Re: Vertical joining

novinosrin — Tue, 16 Oct 2018 22:34:22 GMT

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;

Re: Vertical joining

asinusdk — Tue, 16 Oct 2018 22:40:05 GMT

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