CS 2331 -- SAS PROGRAMMING

Program Assignment 6           Fall 2011                                 30 POINTS

Program is due on Friday, December 2

This program will use the SQL features. You will use the combine table from Program 5 as input.  When you are asked to describe the "FROM", refer to the combine data set name where you merged the three data sets together. Remember, you only have to use the Proc SQL statement once. You can chain many sql statements together and do not need a run statement.  You will need a quit; at the end of the program or the SQL step keeps running, doing nothing by the way, until you close the editor window.

Be sure to put title statements with each sql statement. These are placed at the beginning of the sql statement usually the first line in the statement.

1. The first sql is to List students by class and date of birth
    A. List only the following variables: last first sex class college  dob ( format = weekdate.)
    B. Order by (1) class, and (2) dob in descending order

2. Example of between and statement
    A. List the following variables:  last first college tuition (tuition should have a $ format)
    B. Tuition between 3500 and 5500
    C. Order by tuition in descending order

3. List Students whose Last names start with an "E"
    A. List the following variables:  last first sex
   
4. List the City using the Contains Clause to find Global Village  
    A. List the following variables: first last address city zip
    B. Where the city contains Global Village
    C. Order by last

5. Find students who have an adviser in Business and have earned more than 65 hours
    A. List last first earnhrs college adviser_id
    B. where adviser_id = 'A-BU103' and earned hours is greater than 65
    C.  Order by earnhrs

6. Calculate the average for tuition and the average for tech fee for each college
    A. List college, and average for tuition and tech. Use label and format for dollars on tuition and tech
    B. Group by college

7. Use counts, minimums, maximums, for grade point average (ovgpa)
    A. List class count min max (be sure to format ovgpa with 4.2)
    B. Group by class

8. You are to merge the first table (first) and second table (second) from Program 3 together using SQL statements
    A. Use the following variables : id (used by both to match) last first class sex college resident
    B. Match where the first id matches the second id.
    C. Order by id.

9. Repeat step 8, using the three different parameter combinations of the SQL join statement.  Identify on the output the differences you see with each type of join.
_________________________________________________________________________________________________________________________

In order to save some paper and get more across a page on your output, you should change the font size on the output that you print. In the output window, go to the FILE menu. Click on the PRINT SETUP choice. When you get this menu, click on the FONTS option. You will then get a menu about the different fonts. Make the following selections:

1. Font: SAS Monospace.
2. Font Size: 8
 Turn in the program from the program editor also.