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)