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.
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.
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.
Hint for question 2: Try adding
data _null_; set sasuser.ranch(obs=1); run;
before calling proc print. Check the log messages.
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/
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;
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?
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.
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?
yes
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.