· The total mark for this assignment is 100.

· This homework assignment accounts for 5% of the total marks for this course.

· This homework is due by 11:55pm (Sydney time) on Sunday 9 October 2022 and should be submitted via Moodle.

· Please refer to the document “Format for Answering an SQL Question” to prepare your answers.

· Please save your main answer document as a pdf file. (If you submit a word document, Moodle will convert it to a pdf file; sometimes some content may be distorted.)

· Please also upload your SAS queries via Moodle. You can save a copy of your SAS queries in EG using the Save button (e.g., HW1_z1234567.sas) for the uploading. Note if we need to run your query, we will use this SAS program. If you have created more than one SAS query files, please upload all of them to the assigned file repository on Moodle before the deadline.

The following three questions are pertaining to Orion Star and the data file employee_information given to you in your lab session.

In this homework, you may use SAS Enhancement features in your queries. In each question, you should only use one select statement. Finally, to avoid penalty, please make sure the headers of your result table meet the formatting requirements given in each question (e.g., the number of decimal places and letter cases).

After you finish a query for a question, you may need to answer some additional questions, such as report the number of rows in your result table and the average of an attribute. To count row

numbers, you can easily use “proc sql number;” as your first line of the query, which will display row numbering. Normally the content of the result table can be directly copied and pasted to an Excel spreadsheet, where you can easily count or sum a column. If it cannot be copied and pasted, you may create a table in your query. To do so, you simply insert a row right before your select statement as highlighted in yellow below:

proc sql number;

Create table orion.your_file_name as Select x as name_x, y as name_y From …

Where …

end;

This would save your query result as a SAS dataset (a table), which is copyable. Later, you can even retrieve the data from this newly created table by a query as following:

proc sql number;

Select name_x, name_y From Orion.your_file_name Where …

end;

Question 1 (35%)

Write a query to list all current employees who joined the Sales Department after 1980 as a Sales Representative (level 1) (i.e., Job_Title is Sales Rep. I). Your result should look like the following example table.

Sort your result based on Salary in decreasing order. Please also indicate how many such employees are listed in your result table and their average salary. Follow the instruction to display your result table. Display only the first and the last 10 rows of your result if the table has more than 20 rows.

Question 2 (40%)

Orion Star is giving bonuses to its current employees in the two departments related to sales: Departments of Sales and Sales Management. The bonus for an employee is a percentage of his/her salary, where the percentage depends on the job longevity of the employee in the company. Due to the difference of their work, the bonus rates are different for these two departments, with the bonus rate for the Sales Department 0.5% higher than that for the Sales Management Department in each category. As of today (assuming it is 1 January 2012), the following table shows how the percentage (or the bonus rate) for the employees in the Sales Management Department is calculated:

For example, if an employee in the Sales Department whose duration of tenure is less than 10 years, his/her bonus rate is 2%+0.5%=2.5%. Write a query to generate a report. Your result should look like the following example table.

Hint: to display a number in percentage, use format=percentx.y (e.g., format=percent4.2). The syntax is similar to the dollar format.