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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
gamotte
Rhodochrosite | Level 12

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;

View solution in original post

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Myurathan
Quartz | Level 8
@RW9 Thank you for your quick reply. I made a mistake in the post, and I have corrected it now. table one has to be joined with country_one and country_two to get the "name" column.
RW9
Diamond | Level 26 RW9
Diamond | Level 26
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;
gamotte
Rhodochrosite | Level 12

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.

Myurathan
Quartz | Level 8
@gamotte Yes, I have made a mistake. I have corrected it now in the post
gamotte
Rhodochrosite | Level 12

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;
Myurathan
Quartz | Level 8
@gamotte, Thank you for your help. I have one question how can I use coalesces for numeric columns?
Kurt_Bremser
Super User

When browsing the documentation for the COALESCEC function, you will notice that there is a page for the COALESCE function right next to it. That's the one for numeric values.

This is part of the usefulness of Maxim 1.

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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.

 

Kurt_Bremser
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 2496 views
  • 1 like
  • 5 in conversation