Help using Base SAS procedures

How to simplify the PROC SQL inner join?

Accepted Solution Solved
Reply
Super Contributor
Posts: 394
Accepted Solution

How to simplify the PROC SQL inner join?

[ Edited ]

Hi all:

 

I have the following codes list below.  I am wondering if there is a way to simply them.  Thanks.

 

proc sql;

create table test2Match_class as

select a.*

from test2 as a inner join

test1 as b on a.Analysis=b.Analysis and a.class=b.product;

quit;

 

proc sql;

create table test2Match_component as

select a.*

from test2 as a inner join

test1 as b on a.Analysis=b.Analysis and a.component=b.product;

quit;

 

proc sql;

create table test2Match_product as

select a.*

from test2 as a inner join

test1 as b on a.Analysis=b.Analysis and a.Product=b.Product;

quit;

 

data matchtotal;

set test2Match_class test2Match_component test2Match_product;

run;


Accepted Solutions
Highlighted
Solution
‎02-01-2018 01:26 PM
Super User
Posts: 9,855

Re: How to simplify the PROC SQL inner join?

I think you need brackets:

proc sql;
create table test2Match_class as
select a.*
from test2 as a inner join
test1 as b on a.Analysis=b.Analysis and (a.class=b.product or a.component=b.product or a.product=b.product);
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 23,224

Re: How to simplify the PROC SQL inner join?

Try combining your conditions with an OR statement.

 

proc sql;
create table test2Match_class as
select a.*
from test2 as a inner join
test1 as b on a.Analysis=b.Analysis and a.class=b.product or a.component=b.product or a.product=b.product;



;
quit;
 
Highlighted
Solution
‎02-01-2018 01:26 PM
Super User
Posts: 9,855

Re: How to simplify the PROC SQL inner join?

I think you need brackets:

proc sql;
create table test2Match_class as
select a.*
from test2 as a inner join
test1 as b on a.Analysis=b.Analysis and (a.class=b.product or a.component=b.product or a.product=b.product);
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 290 views
  • 3 likes
  • 3 in conversation