In order to get full credit, you need show all necessary code and output wherever necessary.

1. (40 pts)  Let’s work with the dataset electric.sas7bdat in the SasHelp library. You will practice using SQL to create a new dataset and create and modify variables.

1. Use a PROC SQL step to find the unique years in the dataset and list them below, listing the most recent year first.
   1. Use a PROC SQL step to find the annual average revenue for each customer type and list them below.
   1. Use PROC SQL to create a data table in your WORK library with the following variables:

– a new variable rev2 that will have the same value as revenue, but formatted to have no dollar sign,

– a new variable cust2 that will contain the first letter of the variable customer,

– and use a case logic to create a categorical variable to slice Year into 3 groups like below:

            1994-1999,

2000-2003,

2004 and later.

You don’t need to paste the whole dataset here.  Just the code will be fine.

1.4   Besides creating variables from existing variables, you can also assign values to a new variable, for example,

‘23Jun2023’d as date format=date9. Label=”today’s date” .

Of course, since it’s just another variable, you need to list it in the SELECT clause.

Use another PROC SQL to create a new table and add new variables below:

• ‘dataSource’ by assigning the value “SASHELP.electric” to it,
• ‘today’ with the value of the date when you are doing this assignment, and
• ‘author’ with your name.

• (20 pts) Let’s work with the friends.sas7bdat data, and we will practice using character functions in SQL. Use just one single PROC SQL, create a new data table and add the following variables:

• • Name of the zoo supporter with the first initial followed by a dot, a space, and the last name. For example, “J. Smith” for John Smith. Call it Name with a length of 25. There must be no leading blanks.
  • Using the calculated variables name and existing variable state to create a new variable by concatenating them, separated by a space, a hyphen, and a space. For example “J. Smith – CA”.

• (40 pts) SQL can be very flexible and powerful when creating data tables and variables.  However, SAS data steps can be more convenient in some cases, especially when creating multiple variables using conditional logics.  We will use the salaries.xlsx data file.  After reading it in, use PROC SQL for the following tasks.
  • (Case logic) From the DoB we can calculate the employee’s age as of the date 23Jun2003. Create a categorical variable AgeGroup to have the values “Below 30”, “30-45”, “45-54”, and “55 and above”;

• Find the highest ‘salary’ in each age group, then calculate the mean salary for each age group.  Comment on what you saw.

• Create a new variable to represent the job level of each employee.  Levels should be “director”, “manager”, and “other”.  Create a table to show how many there are in each job level.

• Create a bonus variable of $1,000.00 for employees who are managers or directors, and $200 otherwise.