BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dr2014
Quartz | Level 8

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
dr2014
Quartz | Level 8

@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

5 REPLIES 5
Reeza
Super User

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?

ballardw
Super User

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

dr2014
Quartz | Level 8

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.

ChrisBrooks
Ammonite | Level 13

How are you deriving code2 on table_c?

dr2014
Quartz | Level 8

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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