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.
@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!
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?
@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.
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.
How are you deriving code2 on table_c?
@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!
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.
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.