Hi Experts,
I have the following tables.
data one;
input countryid id value;
datalines;
1 001 5260
1 002 5698
1 003 3256
2 004 5698
2 005 6589
;
run;
data country_one;
input countryid id name;
datalines;
1 001 sam
1 002 jim
1 003 dim
;
run;
data country_two;
input countryid id name;
datalines;
2 004 bill
2 005 jill
;
run;
My current code looks like this.
proc sql;
create table a as
select a.*, b.name
from one as a
left join country_one as b
on a.countryid = b.countryid and a.id =b.id
where a.countryid =1;
quit;
proc sql;
create table b as
select a.*,b.name
from one as a
left join country_two as b
on a.countryid = b.countryid and a.id =b.id
where a.countryid =2;
quit;
data country_one;
set a b;
run;
There should be a better way to do it. Could you guys help me on this?
Thanks in advance.
You can chain the joins and use coalesce function as follows :
proc sql;
create table want as
select a.*, coalescec(b.name, c.name) as name
from one as a
left join country_one as b
on a.countryid = b.countryid and a.id =b.id
left join country_two as c
on a.countryid = c.countryid and a.id =c.id;
quit;
You can use union all as the tables are the same - which begs the question, one which we often ask here, which is why you have the same data in two different datasets which is never a good way of working. Always (except in very specialized circumstances) keep data which is the same in one dataset as it makes processing faster, programming simpler etc.
proc sql; create table want as select * from (select * from country_one union all select * from country_two) where countrid in (1,2); quit;
proc sql; create table want as select a.*,
b.name from one a
left join (select * from country_one union all select * from country_two)
on a.countryid=b.countryid
where countrid in (1,2); quit;
Hello,
What are you trying to do ?
In the following step :
proc sql;
create table a as
select a.*
from one as a
left join country_one as b
on a.countryid = b.countryid and a.id =b.id
where a.countryid =1;
quit;
the join of table country_one is completely useless since you don't
retrieve any column from this dataset nor use its columns to filter the results.
You can chain the joins and use coalesce function as follows :
proc sql;
create table want as
select a.*, coalescec(b.name, c.name) as name
from one as a
left join country_one as b
on a.countryid = b.countryid and a.id =b.id
left join country_two as c
on a.countryid = c.countryid and a.id =c.id;
quit;
seems more of a case where you could use a simple data merge to obtain your results, rather than all the left, right and unions needed.
Pure data step method:
data countries;
set
country_one
country_two
;
by countryid id; /* guarantees correct interleaving */
run;
data want;
merge
one
countries
;
by countryid id;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.