topic Re: How to join 2 tables using 'or' condition on key fields? in SAS Enterprise Guide

How to join 2 tables using 'or' condition on key fields?

ncsthbell — Thu, 03 May 2012 20:16:04 GMT

I am using SAS EG 4.2 and pretty new to it. I am trying to use a query builder to join together 2 tables where the key can exist in one or more columns.

Example:

Data:

TableA - col1 <-- if TableA.col1 value is in either one of the columns on TableB, I want to join the data, can't figure out how to accomplish this.

TableB - col1, col2

I need to join the TableA to TableB if TableA.col1 = (TableB.col1 or TableB.col2)

I tried to build the join for TableA.col1 = TableB.col1, then I went into 'Edit' on the join properties and added 'OR TableA.col1 - TableB.col2)

I get syntax errors because it seems to be expecting the operand to be 'AND', so what I wound up with wad:

TableA.col1 = TableB.col1 AND OR TableA.col1 = TableB.col2

Any suggestions would be greatly appreciated!!!

Thanks

Re: How to join 2 tables using 'or' condition on key fields?

Linlin — Thu, 03 May 2012 22:54:27 GMT

Hi,

there is a workaround. after run your joins, check the code EG generated:

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_ONE_0000 AS

SELECT t1.col1,

t2.col1 AS col11,

t2.col2

FROM WORK.ONE t1, WORK.TWO t2

WHERE (t1.col1 = t2.col1 AND t1.col1 = t2.col2);

QUIT;

change the "AND" to "or",run the code again.

Re: How to join 2 tables using 'or' condition on key fields?

ncsthbell — Fri, 04 May 2012 12:10:55 GMT

Thank you Linlin. I have not used SAS EG (4.2) to create my own code yet. I am using query builder (just learning EG). Do I need to create a 'program' and put the code in it to run?

Re: How to join 2 tables using 'or' condition on key fields?

Linlin — Fri, 04 May 2012 14:19:46 GMT

Hi,

You don't have to create your own code, EG creates the code automaticlly when you use query builder(you can look at the code by clicking "code" tab) ,you only need to modify the code. Please check the attached screenshot. Hope this helps. - Linlin