/*SAS Workshop Lecture 3 - SAS/SQL     Peng Liu 2006/04/22 */

LIBNAME MFE "C:\Documents and Settings\CAL\Desktop\workshop\SAS_L3";

/*Exer01.sas*/
PROC SQL;
SELECT DISTINCT rating FROM MFE.MOVIES;
QUIT;

/*Exer02.sas*/
PROC SQL ;
 SELECT *
  FROM MFE.MOVIES
  ORDER BY category;
QUIT;

/*Exer03.sas*/
PROC SQL;
 SELECT title, category
  FROM MFE.MOVIES
  WHERE category CONTAINS 'Action';
  *WHERE UPCASE(category) LIKE '%ACTION%';
QUIT;

/*Exer04.sas*/
PROC SQL;
 SELECT title, category, rating
  FROM MFE.MOVIES
  WHERE category =* 'Drana';
QUIT;

/*Exer05.sas */
PROC SQL;
 CREATE TABLE ACTION AS
 SELECT title, category
  FROM MFE.MOVIES
  WHERE category CONTAINS 'Action';
QUIT;

/*Exer06.sas*/
PROC SQL;
 CREATE VIEW G_MOVIES AS
  		SELECT title, category, rating
   		FROM MFE.MOVIES
    	WHERE rating = 'G'
     	ORDER BY title;

 SELECT  *  FROM G_MOVIES;    
QUIT;

/*Exer07.sas*/
PROC SQL;
SELECT title, rating,
         CASE rating
              WHEN 'G' THEN 'General' ELSE 'Other'
         END AS Level
    FROM MFE.MOVIES;
QUIT;

/*Exer08.sas*/
PROC SQL;
SELECT title, rating,
	CASE 
		WHEN category='Adventure' THEN 'Exciting'
		WHEN category='Comedy'    THEN 'Fun'
		WHEN category='Suspense' THEN 'Scary'
		ELSE ''
	END AS type
FROM MFE.MOVIES;
QUIT;


/*Exer09.sas*/
PROC SQL;
  SELECT *
    FROM MFE.CUSTOMERS, MFE.MOVIES;
QUIT;


/*Exer10.sas*/
PROC SQL;
  SELECT *
    FROM MFE.MOVIES, MFE.ACTORS
    WHERE MOVIES.title  =  ACTORS.title;
QUIT;

/*Exer11.sas*/
PROC SQL;
  SELECT M.title, M.rating, A.actor_leading
  FROM MFE.MOVIES M, MFE.ACTORS A
    WHERE MOVIES.title  =  ACTORS.title;
QUIT;

/*Exer12.sas*/
PROC SQL;
  SELECT C.cust_no,
              M.title,M.rating,  M.category,
              A.actor_leading
    FROM MFE.CUSTOMERS  C,
         MFE.MOVIES2    M,
         MFE.ACTORS     A
      WHERE C.cust_no = M.cust_no  AND  M.title = A.title;
QUIT;

/*Exer13.sas*/
PROC SQL;
  SELECT M.title, rating,actor_leading
              
  FROM   MFE.MOVIES  M
  INNER JOIN MFE.ACTORS A
  ON M.TITLE  =  A.TITLE;
QUIT;


/*Exer14.sas*/
PROC SQL;
 SELECT MOVIES.title, actor_leading, rating
  FROM MFE.MOVIES M
  LEFT JOIN MFE.ACTORS A
  ON M.title = A.title;
QUIT;

/*Exer15.sas*/
PROC SQL;
 SELECT ACTORS.title, actor_leading, rating
  FROM MFE.MOVIES
  RIGHT JOIN MFE.ACTORS
  ON MOVIES.title = ACTORS.title;
QUIT;

/*Exer16.sas*/
/*Concatenating*/
PROC SQL;
  SELECT * FROM MFE.CUSTOMERS
  OUTER UNION 
  SELECT * FROM MFE.MOVIES;
QUIT;
/*Creating new variables*/
PROC SQL;
  SELECT title, length, category, year, rating,
  		 2006-year as age
  FROM MFE.MOVIES;
QUIT;

/*Exer17.sas*/
/*summarizing data*/
PROC SQL;
  SELECT *,
		  COUNT(title) AS notitle,
		  MAX(year)    AS most_recent,
		  MIN(year)    AS earliest,
		  SUM(length)  AS total_length,
		  NMISS(rating)AS nomissing
  FROM MFE.MOVIES
  GROUP BY rating;
QUIT;

/*Exer18.sas*/
/*Editing Observation*/
PROC SQL NOPRINT;
INSERT INTO MFE.CUSTOMERS
  VALUES(1 'Peng');
INSERT INTO MFE.CUSTOMERS
  SET Cust_no=2,Name='Sasha';
QUIT;

/*Exer19.sas*/
/*deleting rows*/
PROC SQL;
 DELETE
  FROM MFE.MOVIES
  WHERE length LE 100;
QUIT;

/*Exer20.sas*/
/*droping variables*/
PROC SQL;
 CREATE TABLE NEW (DROP=rating) AS
 SELECT *
  FROM MFE.MOVIES;
QUIT;

/*Exer21.sas*/
/*Updating Observation*/
PROC SQL NOPRINT;
UPDATE MFE.CUSTOMERS
  SET Name='Liu'
  WHERE Cust_no=1;
QUIT;