Sentry Page Protection

Please Wait...

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)

Next