BookmarkSubscribeRSS Feed
ncsthbell
Quartz | Level 8

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

3 REPLIES 3
Linlin
Lapis Lazuli | Level 10

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.

ncsthbell
Quartz | Level 8

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?

Linlin
Lapis Lazuli | Level 10

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


query builder.png

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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