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,  

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

9 REPLIES 9
Reeza
Super User

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. 

PGStats
Opal | Level 21

Hint for question 2: Try adding 

 

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

 

before calling proc print. Check the log messages.

PG
Patrick
Opal | Level 21

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/ 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

 

ybz12003
Rhodochrosite | Level 12
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? 

ybz12003
Rhodochrosite | Level 12
  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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

ybz12003
Rhodochrosite | Level 12

yes

ballardw
Super User

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.

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!

How to Concatenate Values

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.

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
  • 9 replies
  • 830 views
  • 2 likes
  • 6 in conversation