topic Re: Joinng more than one table in SAS Programming

Joinng more than one table

Myurathan — Fri, 15 Mar 2019 10:56:18 GMT

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.

Re: Joinng more than one table

RW9 — Fri, 15 Mar 2019 10:47:16 GMT

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;

Re: Joinng more than one table

Myurathan — Fri, 15 Mar 2019 10:54:58 GMT

@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.

Re: Joinng more than one table

gamotte — Fri, 15 Mar 2019 10:55:40 GMT

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.

Re: Joinng more than one table

Myurathan — Fri, 15 Mar 2019 10:57:02 GMT

@gamotte Yes, I have made a mistake. I have corrected it now in the post

Re: Joinng more than one table

RW9 — Fri, 15 Mar 2019 10:59:52 GMT

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;

Re: Joinng more than one table

gamotte — Fri, 15 Mar 2019 11:03:00 GMT

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;

Re: Joinng more than one table

VDD — Fri, 15 Mar 2019 11:36:53 GMT

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.

Re: Joinng more than one table

Kurt_Bremser — Fri, 15 Mar 2019 11:42:36 GMT

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;

Re: Joinng more than one table

Myurathan — Fri, 15 Mar 2019 11:53:14 GMT

@gamotte, Thank you for your help. I have one question how can I use coalesces for numeric columns?

Re: Joinng more than one table

Kurt_Bremser — Fri, 15 Mar 2019 11:59:00 GMT

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.