Sentry Page Protection
Coding Exercise (Answer)
Tips: copy the code to your SAS Studio for better viewing.
** Exercise 1 **;
proc sql;
select "Total # of hosts", count(distinct hostid) as value
from host
union
select "Median Age", median(age) as value
from host
union
select "Median Income", median(income) as value
from host
;
quit;
** Exercise 2 **;
proc sql outobs=5 nowarn;
create table top5 as
select neighborhood, mean(price) as avgprice
from property
group by neighborhood
order by avgprice desc;
quit;
** Exercise 3 **;
** List hosts whose properties in top 5 **;
proc sql outobs=5 nowarn;
create table top5 as
select neighborhood, mean(price) as avgprice
from property
group by neighborhood
order by avgprice desc;
quit;
proc sql;
select hostid, name, h.propertyid, p.neighborhood
from host h, property p, top5 t
where h.propertyid = p.propertyid
and p.neighborhood = t.neighborhood;
quit;
** Exercise 4 **;
** Get list of neighborhood where Jan revenue > 3500 **;
** Task 1: Count # of days each property rented out in Jan **;
proc sql;
select propertyid, count(*) as ndays
from rentaldata
where rented = "Yes"
group by propertyid;
quit;
** Task 2: Calculate the monthly income **;
proc sql;
select p.propertyid, neighborhood, price,
r.ndays, (price*0.9)*r.ndays as mthrev
from property p,
(select propertyid, count(*) as ndays
from rentaldata
where rented = "Yes"
group by propertyid) as r
where p.propertyid = r.propertyid;
quit;
** Task 3: Calculate the average monthly revenue for each neighborhood **;
proc sql;
create table mthrev as
select p.propertyid, neighborhood, price,
r.ndays, (price*0.9)*r.ndays as mthrev
from property p,
(select propertyid, count(*) as ndays
from rentaldata
where rented = "Yes"
group by propertyid) as r
where p.propertyid = r.propertyid;
select neighborhood, mean(mthrev) as mrev
from mthrev
group by neighborhood
having mrev > 3500
order by mrev desc;
quit;
** Exercise 5: **;
** Task 1: Count number of properties in each three neighborhood **;
proc sql;
select neighborhood, count(*) as n
from property
where neighborhood
in ("Dufferin Grove (83)"
"Broadview North (57)"
"Trinity-Bellwoods (81)")
group by neighborhood
order by n;
quit;
** Task 2: Compute average rating for each three neighborhood **;
proc sql;
select p.neighborhood, mean(rating) as avgrate
from rentaldata r, property p
where p.propertyid = r.propertyid and
neighborhood
in ("Dufferin Grove (83)"
"Broadview North (57)"
"Trinity-Bellwoods (81)")
group by p.neighborhood;
quit;
** Exercise 6 **;
** Find out which country has the most renters in Dufferin Grove **;
Proc sql;
select country, count(g.guestid) as n
from guest g, rentaldata r, property p
where g.guestid = r.guestid and
r.propertyid = p.propertyid and
neighborhood = "Dufferin Grove (83)"
group by country
order by n desc;
quit;
** United States and China have the most renters in the neighborhood of Dufferin Grove. **;
** Exercise 7 **;
** Task 1 : create subtotal column **;
proc sql;
select item, quantity, price,
price * quantity as subtotal
from order1 o1 left join walmart w
on item = product;
quit;
** Task 2: compute total (sum of subtotal) **;
proc sql;
create table temp as
select sum(subtotal) as Subtotal
from
(select item, quantity, price,
price * quantity as subtotal
from order1 o1 left join walmart w
on item = product);
create table walmartprice as
(select "Subtotal (no shipping)" as col,
subtotal as Cost format=dollar10.2
from temp)
outer union corr
(select "Shipping" as col,
5 as Cost format=dollar10.2
from temp)
outer union corr
(select "Total" as col,
subtotal + 5 as Cost format=dollar10.2
from temp);
quit;
** The total when purchasing from Walmart is $45.94 **;
** Exercise 8 **;
** Task 1 : create subtotal column **;
proc sql;
select item, quantity, price,
price * quantity as subtotal
from order1 o1 left join amazon a
on item = product;
quit;
** Task 2: compute total (sum of subtotal) **;
proc sql;
create table temp as
select sum(subtotal) as Subtotal
from
(select item, quantity, price,
price * quantity as subtotal
from order1 o1 left join amazon a
on item = product);
create table amazonprice as
(select "Subtotal (no shipping)" as col,
subtotal as Cost format=dollar10.2
from temp)
outer union corr
(select "Shipping" as col,
5 as Cost format=dollar10.2
from temp)
outer union corr
(select "Total" as col,
subtotal + 5 as Cost format=dollar10.2
from temp);
quit;
** The total when purchasing from Amazon is $43.03 **;
** Yes, it is cheaper to purchase on Amazon than Walmart for this particular list of items. **;
** Exercise 9 **;
** Task 1: Join the Walmart and Amazon table **;
proc sql;
select w.product,
w.price as walprice,
a.price as amzprice
from walmart w, amazon a
where w.product = a.product;
quit;
** Task 2: Compute the sub-total based on the lower price between Walmart and Amazon **;
proc sql;
create table both as
select product,
walprice,
amzprice,
min(walprice, amzprice) as lowerprice,
calculated lowerprice * quantity as subtotal
from order1 o1 left join
(select w.product,
w.price as walprice,
a.price as amzprice
from walmart w, amazon a
where w.product = a.product) c
on o1.item = c.product;
quit;
** Task 3: Compute the total based on purchasing from both companies **;
proc sql;
create table temp as
select sum(subtotal) as Subtotal
from
(select item, quantity, lowerprice as price,
lowerprice * quantity as subtotal
from order1 o1 left join both b
on item = product);
create table combinedprice as
(select "Subtotal (no shipping)" as col,
subtotal as Cost format=dollar10.2
from temp)
outer union corr
(select "Shipping" as col,
10 as Cost format=dollar10.2
from temp)
outer union corr
(select "Total" as col,
subtotal + 10 as Cost format=dollar10.2
from temp);
quit;
** The total when purchasing from both walmart and Amazon is $42.66 **;
** Exercise 10 **;
proc sql;
select w.col,
w.cost as walmartcost,
a.cost as amazoncost,
c.cost as combinedcost
from walmartprice w, amazonprice a, combinedprice c
where w.col = a.col and w.col = c.col;
quit;
** Bonus Exercise **;
%macro compare (intable);
proc sql noprint;
create table temp as
select sum(subtotal) as Subtotal
from
(select item, quantity, price,
price * quantity as subtotal
from &intable o1 left join walmart w
on item = product);
create table walmartprice as
(select "Subtotal (no shipping)" as col,
subtotal as Cost format=dollar10.2
from temp)
outer union corr
(select "Shipping" as col,
5 as Cost format=dollar10.2
from temp)
outer union corr
(select "Total" as col,
subtotal + 5 as Cost format=dollar10.2
from temp);
quit;
proc sql noprint;
select item, quantity, price,
price * quantity as subtotal
from &intable o1 left join amazon a
on item = product;
quit;
proc sql noprint;
create table temp as
select sum(subtotal) as Subtotal
from
(select item, quantity, price,
price * quantity as subtotal
from &intable o1 left join amazon a
on item = product);
create table amazonprice as
(select "Subtotal (no shipping)" as col,
subtotal as Cost format=dollar10.2
from temp)
outer union corr
(select "Shipping" as col,
5 as Cost format=dollar10.2
from temp)
outer union corr
(select "Total" as col,
subtotal + 5 as Cost format=dollar10.2
from temp);
quit;
proc sql noprint;
select w.product,
w.price as walprice,
a.price as amzprice
from walmart w, amazon a
where w.product = a.product;
quit;
proc sql noprint;
create table both as
select product,
walprice,
amzprice,
min(walprice, amzprice) as lowerprice,
calculated lowerprice * quantity as subtotal
from &intable o1 left join
(select w.product,
w.price as walprice,
a.price as amzprice
from walmart w, amazon a
where w.product = a.product) c
on o1.item = c.product;
quit;
proc sql noprint;
create table temp as
select sum(subtotal) as Subtotal
from
(select item, quantity, lowerprice as price,
lowerprice * quantity as subtotal
from &intable o1 left join both b
on item = product);
create table combinedprice as
(select "Subtotal (no shipping)" as col,
subtotal as Cost format=dollar10.2
from temp)
outer union corr
(select "Shipping" as col,
10 as Cost format=dollar10.2
from temp)
outer union corr
(select "Total" as col,
subtotal + 10 as Cost format=dollar10.2
from temp);
quit;
proc sql;
select w.col,
w.cost as walmartcost,
a.cost as amazoncost,
c.cost as combinedcost
from walmartprice w, amazonprice a, combinedprice c
where w.col = a.col and w.col = c.col;
quit;
%mend;
%compare(order1)
%compare(order2)
%compare(order3)
%compare(order4)