topic Re: Proc SQL question? in SAS Programming

Proc SQL question?

ybz12003 — Mon, 28 Nov 2016 03:35:48 GMT

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.

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;

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.

Re: Proc SQL question?

Reeza — Mon, 28 Nov 2016 03:51:33 GMT

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.

Re: Proc SQL question?

PGStats — Mon, 28 Nov 2016 04:19:46 GMT

Hint for question 2: Try adding

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

before calling proc print. Check the log messages.

Re: Proc SQL question?

Patrick — Mon, 28 Nov 2016 05:45:39 GMT

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/

Re: Proc SQL question?

RW9 — Mon, 28 Nov 2016 09:38:25 GMT

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;

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;

Re: Proc SQL question?

ybz12003 — Mon, 28 Nov 2016 14:42:33 GMT

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?

Re: Proc SQL question?

ybz12003 — Mon, 28 Nov 2016 14:49:53 GMT

  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.

Re: Proc SQL question?

ballardw — Mon, 28 Nov 2016 15:37:11 GMT

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.

Re: Proc SQL question?

RW9 — Mon, 28 Nov 2016 15:42:41 GMT

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?

Re: Proc SQL question?

ybz12003 — Mon, 28 Nov 2016 18:11:34 GMT

yes