topic Re: full join versus merge in SAS Data Management

full join versus merge

dr2014 — Thu, 17 Aug 2017 21:57:43 GMT

Hi,

I am looking to use a full join versus a merge. Below are 2 datasets Table_a and Table_b that I want to do a full join on to get Table_c:

table_a

id	Code1	date	source
1	h	d1	abc
1	h	d2	abc
1	c1	d3	abc
1	c2	d4	abc

table_b

id	Code2	date	source
1	n1	D1	abc
1	n1	D4	abc
1	n2	D5	abc

table_c

id	Code1	Code2	date	source
1	h	ndc1	d1	abc
1	h	ndc1	d2	abc
1	c1	ndc2	d3	abc
1	c2		d4	abc

would the below code help me acheive table_c?

select *

from table_a c full join table_b t

on c.id=t.id and c.date=t.date

Please let me know asap. Thanks.

Re: full join versus merge

Reeza — Thu, 17 Aug 2017 22:05:13 GMT

I'm confused, are you asking us to import your data (including creating a step to import the data), run your code and tell us if it gives you the answer you want?

Re: full join versus merge

ballardw — Thu, 17 Aug 2017 22:43:49 GMT

@dr2014 wrote:

Hi,

I am looking to use a full join versus a merge. Below are 2 datasets Table_a and Table_b that I want to do a full join on to get

would the below code help me acheive table_c?

select *

from table_a c full join table_b t

on c.id=t.id and c.date=t.date

Please let me know asap. Thanks.

Without a Proc Sql start, a create table as clause, at least one ; and a quit; no.

Otherwise run it and see.

Re: full join versus merge

dr2014 — Thu, 17 Aug 2017 23:11:56 GMT

Hi @Reeza and @ballardw,

I provided the tables as examples. The full code is here:

proc sql;

create table table_c as

select *

from table_a c full join table_b t

on c.id=t.id and c.date=t.date;

quit;

I ran the above code and it gave me an error saying there are duplicates in 'id' column. I also realized joins do not overlay columns. I was wondering if there was way through joins to go about getting table_c with unique rows for values in code1 and code2. Hope this explains what I am trying to acheive. Thanks.

Re: full join versus merge

ChrisBrooks — Fri, 18 Aug 2017 01:06:15 GMT

How are you deriving code2 on table_c?

Re: full join versus merge

dr2014 — Fri, 18 Aug 2017 01:32:11 GMT

@ChrisBrooks I got what I needed. I wasn't going about it the right way. To get to table_c , here is the new approach . I first compressed the perod_id and date fields to get a unique id , relabeled the variables code1 and code2 in table_a and table_b respectively as 'code' and then stacked table_a and table_b . I then used conditional logic using the unique id and its associated unique values in the code variable to get to table_c as I needed an addtional derived variabale as well.

Thanks!