Help using Base SAS procedures

Full Joins

Reply
Contributor
Posts: 61

Full Joins

I am trying to perform a full join (include all matches and non-matches based on two conditions keeping variables from both tables)
I am attempting to join by two conditions becuase "LCODE" in dataset A can be found as 'DCODE1" or "SCODE1" in two different variables in dataset B.    I tried to increase buffersize as I read that could possibly resolve- it didnt.

I am getting this error from SAS when trying to perform this join.  Can anyone help me?  Thank you.

"The execution of this query involves performing one or more Cartesian product joins that can not be optimized."

PROC SQL buffersize=256000;
SELECT *         
FROM EOY A FULL JOIN WC B ON
(A.LCODE = B.DCODE1) OR (A.LCODE = B.SCODE1);
QUIT;

PROC Star
Posts: 7,480

Re: Full Joins

Posted in reply to Suzanne_Ed

That message isn't indicating an error .. just that proc sql couldn't optimize the request.  Are you getting the desired results?

If not, some example data and desired results from that data would be helpful.

Contributor
Posts: 61

Re: Full Joins

It fails to continue to execute.  A pop-up window tells me " Output WINDOW FULL" and just stops. 

PROC Star
Posts: 7,480

Re: Full Joins

Posted in reply to Suzanne_Ed

Do you even want the results to appear on screen?  Why not include a first line that creates a file?  i.e,

create table replace_this_with_whatevertablenameyouwant as

Contributor
Posts: 61

Re: Full Joins

Thank you Mr. Tabachneck!   I created a table and that resolved the pop up box.  Arghhh still trying to learn PROC SQL to join.  Been using 'merge' statement and trying to learn joins.  Thank you again!  

Respected Advisor
Posts: 3,156

Re: Full Joins

Posted in reply to Suzanne_Ed

PROC SQL buffersize=256000 noprint;

PROC Star
Posts: 7,480

Re: Full Joins

: that won't help as she isn't creating a table.  Conversely, if she does add a create table statement, the noprint is implicit.

Contributor
Posts: 61

Re: Full Joins

Thank you to Hai Kuo too!  Really appreciate your help too!! 

Ask a Question
Discussion stats
  • 7 replies
  • 227 views
  • 3 likes
  • 3 in conversation