Proc SQL question?

Accepted Solution Solved
Reply
Super Contributor
Posts: 309
Accepted Solution

Proc SQL question?

Hello,  

 

Due to I don't have SAS at home, I wish someone could help me out for two questions below.  Many thanks.

 

1.  Which one is not right based on the following two PROC SQL program?  Why?  In my opinion, both are not correct.

A.

proc sql;

select product.*, cost.unitcost, sales.quantity

from product p, cost c, sale s

where p.item = c.item and p.item = s.item;

quit;

B. 

proc sql;

select p.*, cost.unitcost, sales.quantity

from product , cost , sale

where product.item = cost.item and product.item = sales.item;

quit;

 

2. Why the PRINT procedure fails?

229 data sasuser.ranch sasuser.condo / view = sasuser.ranch;

230 set sasuser.houses;

231 if style='RANCH' then output sauser.ranch;

232 else if style='CONDO' then output sasuser.condo;

233 run;

NOTE: DATA STEP view saved on file SASUSER.RANCH. NOTE:

A stored DATA STEP view cannot run under a different operating system.

234 235 proc print data = sasuser.condo;

ERROR: File sasuser.condo data does not exist.

236 run;

NOTE: The SAS system stopped processing this step because of errors.


Accepted Solutions
Solution
‎12-03-2016 10:48 AM
Super User
Posts: 11,134

Re: Proc SQL question?

Many errors in B

B. 

proc sql;
select p.*, cost.unitcost, sales.quantity
       ^                       ^
from product , cost , sale

where product.item = cost.item and product.item = sales.item;
                                                      ^
quit;

The syntax on select is dataset.variable (or an alias defined on the From clause) your P.* needs to reference a data set, you only have product, cost and sale. You also refer to sales.quantity SALES is not the same as SALE

 

 

Your example A has the SAME issue with Sale vs Sales though is likely to work (assuming the variable names are correct/present in the data sets) if you use:

select p.*, c.unitcost, s.quantity

because you had defined the Aliases p, c and s.

View solution in original post


All Replies
Super User
Posts: 19,167

Re: Proc SQL question?

1. There's issues with both. Not sure if some are typos or what though 

2. There must not have been any obs outputted to the condo dataset. Do the cases match? Comparisons are case sensitive. 

Respected Advisor
Posts: 4,820

Re: Proc SQL question?

Hint for question 2: Try adding 

 

data _null_; set sasuser.ranch(obs=1); run;

 

before calling proc print. Check the log messages.

PG
Respected Advisor
Posts: 4,137

Re: Proc SQL question?

Due to I don't have SAS at home

There is a free SAS version available for non commercial use. http://support.sas.com/software/products/university-edition/ 

Super User
Super User
Posts: 7,720

Re: Proc SQL question?

a)/b):

At minimum there is a typo at carat:

proc sql;
  select product.*, cost.unitcost, sales.quantity
                                       ^
  from product p, cost c, sale s
  where p.item = c.item and p.item = s.item;
quit;

2)

Why are you putting your data in sasuser?  Use work for wokring data:

data ranch condo;
  set sasuser.houses;
  if style='RANCH' then output ranch;
  else if style='CONDO' then output condo;
run;
proc print data=condo;
run;

 

 

Super Contributor
Posts: 309

Re: Proc SQL question?

proc sql;
  select product.*, cost.unitcost, sale.quantity
                                       ^
  from product p, cost c, sale s
  where p.item = c.item and p.item = s.item;
quit;

If the program is list as above, should it put "as" before "p", "c" and "s"? Or it could be omissed? 

Super Contributor
Posts: 309

Re: Proc SQL question?

  from product as p, cost as c, sale as s
  

like that?  or

 

  from product p, cost c, sale s
  

 the above is working too.

Super User
Super User
Posts: 7,720

Re: Proc SQL question?

I would go through an SQL tutorial to understand some basic concepts such as aliasing.  In this:

proc sql;
  select product.*, cost.unitcost, sales.quantity
                                       ^
  from product p, cost c, sale s
  where p.item = c.item and p.item = s.item;
quit;

there is a dataset called "sale" which you alias as "s", however in your select statement you use something called "sales" which matches neither a dataset nor an alias.  Is this some sort of training material you are working through?

Super Contributor
Posts: 309

Re: Proc SQL question?

yes

Solution
‎12-03-2016 10:48 AM
Super User
Posts: 11,134

Re: Proc SQL question?

Many errors in B

B. 

proc sql;
select p.*, cost.unitcost, sales.quantity
       ^                       ^
from product , cost , sale

where product.item = cost.item and product.item = sales.item;
                                                      ^
quit;

The syntax on select is dataset.variable (or an alias defined on the From clause) your P.* needs to reference a data set, you only have product, cost and sale. You also refer to sales.quantity SALES is not the same as SALE

 

 

Your example A has the SAME issue with Sale vs Sales though is likely to work (assuming the variable names are correct/present in the data sets) if you use:

select p.*, c.unitcost, s.quantity

because you had defined the Aliases p, c and s.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 319 views
  • 2 likes
  • 6 in conversation