SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

full join versus merge

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 124
Accepted Solution

full join versus merge

 

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.


Accepted Solutions
Solution
‎08-18-2017 09:37 AM
Frequent Contributor
Posts: 124

Re: full join versus merge

@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!

View solution in original post


All Replies
Super User
Posts: 19,058

Re: full join versus merge

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?

Super User
Posts: 11,114

Re: full join versus merge


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.

Frequent Contributor
Posts: 124

Re: full join versus merge

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.

Super Contributor
Posts: 392

Re: full join versus merge

How are you deriving code2 on table_c?

Solution
‎08-18-2017 09:37 AM
Frequent Contributor
Posts: 124

Re: full join versus merge

@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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 204 views
  • 0 likes
  • 4 in conversation