topic Merging data in a specific way in SAS Programming

Merging data in a specific way

abdulla — Mon, 21 Sep 2020 07:53:59 GMT

Table X

gvkey	fyear	Name
1004	1994	a
1004	1995	a
1004	1996	b
1004	1997	b

Table Y

gvkey	fyear	Name
1004	1992	a
1004	1993	a
1004	1996	e
1004	1997	f

I want to merge the above two tables in the following way

gvkey	fyear	Name
1004	1992	a
1004	1993	a
1004	1994	a
1004	1995	a
1004	1996	b
1004	1997	b

You can see that I want every observation from table X but from table Y I want only those observations that matches GVKEY but don't match fyear. From table Y if gvkey and year matches, I don't want those observations to be included in my result. Can anyone help me with how I can do this by using proc SQL? I tried to do that using left join but I don't get the desired result.

Re: Merging data in a specific way

ChrisNZ — Mon, 21 Sep 2020 08:13:22 GMT

This works for your sample:

data HAVE;
  merge Y X;
  by GVKEY FYEAR ;
run;

Obs	gvkey	fyear	Name
1	1004	1992	a
2	1004	1993	a
3	1004	1994	a
4	1004	1995	a
5	1004	1996	b
6	1004	1997	b

Re: Merging data in a specific way

Ksharp — Mon, 21 Sep 2020 10:59:23 GMT

data X;
infile cards expandtabs truncover;
input gvkey	fyear	Name $;
cards;
1004	1994	a
1004	1995	a
1004	1996	b
1004	1997	b
;

data Y;
infile cards expandtabs truncover;
input gvkey	fyear	Name $;
cards;
1004	1992	a
1004	1993	a
1004	1996	e
1004	1997	f
;

proc sql;
create table want as
select * from x
union all
select * from y 
 where not exists(select * from x where gvkey=y.gvkey and fyear=y.fyear)
order by 1,2;
quit;