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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.