Answers to Practice Problems in
proc sql document
Solutions using Base SAS.

1. * Create a new dataset with the varable dropped;
   data new_drugstore;
      set drugstore;
      drop store_id;

2. proc datasets;
      modify drugstore;
      rename store_id = storeid;

3. * Create new dataset with observation added;
   data new_obs;
      product = 'cough medicine';
      chain = 'CVS';
      price = 5.11;

   data new_drugstore;
      set drugstore new_obs;

4. data result;
      set drugstore;
      if store_id = 305;
      keep price;

5. data result;
      set drugstore;
      if product='aspirin';
      keep store_id;

6. data result;
      set drugstore;
      if chain='CVS';
      keep store_id;

   proc sort data=result nodup;
      by store_id;

7. proc sort data=drugstore;
      by descending price;

8. proc sort data=drugstore;
      by brand product;

9. proc means data=drugstore min max mean;
      var price;

10. proc means data=drugstore min max mean;
       class chain, product;
       var price;
      
11. proc means data=drugstore min max mean;
       class chain;
       var price;

For the rest of the problems, first sort 
drugstore2 and drugstore_info by store_id.
   
12. data answer;
       set drugstore_info;
       if city = 'Chicago';
       keep store_id phone_number;

13. data answer;
       set drugstore2 drugstore_info;
          by store_id;
       if chain = 'Walgreens';
       keep store_id phone_number;

14. data answer;
       merge drugstore2 drugstore_info;
          by store_id;
       if chain = 'Walgreens' and product = 'aspirin';
       keep price;
    proc means mean;

15. data answer;
       set drugstore2;
       keep product price;
    proc sort nodup;
       by product;
    proc means mean;
       by product;