BookmarkSubscribeRSS Feed
SannaSanna
Quartz | Level 8

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;

7 REPLIES 7
art297
Opal | Level 21

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.

SannaSanna
Quartz | Level 8

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

art297
Opal | Level 21

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

SannaSanna
Quartz | Level 8

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!  

Haikuo
Onyx | Level 15

PROC SQL buffersize=256000 noprint;

art297
Opal | Level 21

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

SannaSanna
Quartz | Level 8

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 991 views
  • 3 likes
  • 3 in conversation