topic Re: Full Joins in SAS Procedures

Full Joins

SannaSanna — Wed, 26 Dec 2012 16:24:13 GMT

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;

Re: Full Joins

art297 — Wed, 26 Dec 2012 16:33:15 GMT

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.

Re: Full Joins

SannaSanna — Wed, 26 Dec 2012 16:38:05 GMT

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

Re: Full Joins

art297 — Wed, 26 Dec 2012 16:41:44 GMT

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

Re: Full Joins

Haikuo — Wed, 26 Dec 2012 16:42:19 GMT

PROC SQL buffersize=256000 noprint;

Re: Full Joins

art297 — Wed, 26 Dec 2012 16:44:55 GMT

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

Re: Full Joins

SannaSanna — Wed, 26 Dec 2012 16:47:17 GMT

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!

Re: Full Joins

SannaSanna — Wed, 26 Dec 2012 16:49:27 GMT

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