BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

Hello:

 

I ran the codes below.  I found some warning and note messages shown up.  In addition, the PROC SQL is going forever, it never stopped.   Please advice how to fix it.  Thank you!

 

proc sql;

create table MasterP as

select

a.p,

b.p,

c.p

 

from

 class as a,

 component as b,

 product as c

 

where

a.p eq . or b.p eq . or c.p eq .;

quit;

 

NOTE: The execution of this query involves performing one or more Cartesian product joins that

can not be optimized.

WARNING: Variable P already exists on file WORK.MASTERP.

WARNING: Variable P already exists on file WORK.MASTERP.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@ybz12003 wrote:

Yes.  My plan was looking for the missing value in P columns of A, B, and C tables.   Thanks.


Then just do this:

data want;
set
  class
  component
  product
;
where p = .;
run;

You will end up with quite a wide structure, but it can't be more than nobs(class) + nobs(component) + nobs(product) observations. Instead of nobs(class) * nobs(component) * nobs(product)!

View solution in original post

14 REPLIES 14
PeterClemmensen
Tourmaline | Level 20

1 - Show a sample of your data for us to debug your program. We can't see your data, so we can't see your problem

2 - Define aliases for commonly named variables (in this case p) like this

 

select
a.p as a_p,
b.p as b_p,
c.p as c_p
from

3 - You are most likely missing some condition in your where statement, since as of not you are creating at least one Cartesian Product, which is rarely desired.

ybz12003
Rhodochrosite | Level 12

Each table contains at least 3000 observation.   I can't show it here.

PeterClemmensen
Tourmaline | Level 20

Not asking you to post 9000 lines of data, just a simple outline of the structure of you data, preferibly in the form of data steps with a few observations, so it is easy to work with 🙂

 

 

Kurt_Bremser
Super User

@ybz12003 wrote:

Each table contains at least 3000 observation.   I can't show it here.


Then you could easily end up with 27.000.000.000 observations in your resulting dataset. Just run your SQL on subsets with 100 observations each, and you'll see what happens.

If you just post those subsets as datasteps here, and an example for the expected result, we can easily show you the code that can do it.

Astounding
PROC Star

If you're going to use SQL, you will need to learn about what a Cartesian product is.

 

In this case, let's say that each source (A, B, and C) contains 1,000 observations with a missing value for P.  Your request is asking for a table with a billion observations in it.  No wonder it is taking a while.  You would be much better served to use a DATA step:

 

data missings;

set class component product;

where p=.;

run;

ybz12003
Rhodochrosite | Level 12

Hi Astounding:

 

I did have a data step to do this inquire.  I would like to use Proc SQL so that I could confirm the one I created was right and save the merge step.  Thanks.

PeterClemmensen
Tourmaline | Level 20

What does your data look like? 

Astounding
PROC Star

In that case, take a look at UNION.  It also has its tricky aspects, but is probably the right tool for the job for QC/comparison purposes.  It's just barely beyond my SQL knowledge, but I'm sure somebody can post a reasonably short example.

Kurt_Bremser
Super User

First of all, you need to give the variables new names if you want to see them in the output:

select
  a.p as p_a,
  b.p as p_b,
  c.p as p_c

That would take care of the NOTEs

What happens then is: The first obs in class where p is missing is joined with ALL obs (regardless of values) from the other two datasets. The same for the next obs that has p missing, and so on. The same also happens for all observations with missing p in the other two datasets, so you get a real lot of output observations. Bottom line: be careful with your where conditions, especially when no join on a key variable is defined.

ybz12003
Rhodochrosite | Level 12

Hi KurtBremser:

The way you are doing will end up create three new columns p_a, p_b and p_c.  What about if I would like to generate all of them in single column?  Also, I have data step to do this.  I just want to try the PROC SQL way to confirm my previous work.

Kurt_Bremser
Super User

It would be mighty helpful if you posted that data step code.

This:

proc sql;
create table MasterP as
select p from class
where p = .
union all
select p from component
where p = .
union all
select p from product
where p = .
;
quit;

will give you all the missing values stacked on top of each other. The number of observations will be the sum (not the product) of the missing's in the datasets.

ybz12003
Rhodochrosite | Level 12

Yes.  My plan was looking for the missing value in P columns of A, B, and C tables.   Thanks.

Kurt_Bremser
Super User

@ybz12003 wrote:

Yes.  My plan was looking for the missing value in P columns of A, B, and C tables.   Thanks.


Then just do this:

data want;
set
  class
  component
  product
;
where p = .;
run;

You will end up with quite a wide structure, but it can't be more than nobs(class) + nobs(component) + nobs(product) observations. Instead of nobs(class) * nobs(component) * nobs(product)!

ybz12003
Rhodochrosite | Level 12

Thanks, it works.

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
  • 14 replies
  • 2522 views
  • 2 likes
  • 4 in conversation