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

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 937 views
  • 0 likes
  • 4 in conversation