Part A:

Using the supplied code below create the FinalCustomerOrders.sas program to create the work.customer_fin data set. This code is shown below. It eliminates the problems in the orion.customer data set, which breaks on SAS Studio. In addition, it drops the Personal_ID variable from the new data set. Dont forget to run libname to gain access to the orion library.

data work.customer_fin;

set orion.customer;

if customer_ID NE 19 and customer_ID NE 33 and customer_ID NE 61;

drop Personal_ID;

run;

Add a new Data step to create a new data set called work.customer_fin_P, from the work.customer_fin data set, which adds a new character type variable called Personal_ID with a length of 15. The value of this variable will be formed from a combination and transformation of several of the variables in the work.customer_fin data set. You will need to review the date functions, the character transforming functions and the functions that change variables from numeric to character. An example of a good Personal_ID value would be: US1020F4SAT

1. Country

2. Customer_Type_ID: Convert numeric to character.

3. Gender

4. Birth_Date: Just the Quarter of the Year it occurs. Convert numeric to character

5. Birth_Date: Just the 3-character Day of the Week abbreviation in uppercase.

Convert numeric to character using format: $DOWNAME3. Make sure to eliminate any blank spaces in the Personal_ID. The new data set, work.customer_fin_P should contain the same variables as the work.customer_fin data set, EXCEPT for Customer_FirstName, Customer_LastName, Customer_Address, Street_ID, and Street_Number. Email me for a list of functions that will suffice for this step, if you cant figure them out.

Add a Proc step that displays the following three variables of the first 10 observations of the work.customer_fin_P data set:

Customer_ID

Personal_ID

Customer_Name

Part B:

Add a Data step that creates the work.orders_fin data set from the orion.orders data set. Add in a new variable called Ship_Time, which holds the number of days between the Order_Date and Delivery_Date. Remember dates are numeric and can be used in calculations. The new data set should not contain the Employee_ID or Delivery_Date variables.

Add a Proc step that displays the first 10 observations of the work.orders_fin data set.

Part C:

Add a Data step that creates the work.order_item_fin data set from the orion.order_item data set. Add in a new variable called Profit, which holds the profit from the sale of that particular order_item.

This value is calculated as Total_Retail_Price-(Quantity* CostPrice_Per_Unit).

The new data set should only contain the Order_ID, Total_Retail_Price and Profit variables.

Add a Proc step that displays the first 10 observations of the work.order_item_fin data set.

Part D:

Note: The work.order_item_fin data set can have multiple observations per Order_ID, if more than one product was ordered.

1) Add a Proc step that sorts the work.orders_fin data set into the work.orders_fin_OI data set on the Order_ID variable.

2) Add a Proc step that sorts the work.order_item_fin data set into the work.order_item_fin_OI data set on the Order_ID variable.

3) Add a Data step that merges the work.orders_fin_OI and work.order_item_fin_OI data sets on the Order_ID variable into the work.orders_order_item_fin data set. Keep only those observations which have matches in both data sets (Use in= option).

4) Add a Proc step that displays the first 10 observations of the work.orders_order_item_fin data set.

Part E:

Note: The work.orders_order_item_fin data set can have multiple observations per Order_ID, if more than one product was ordered. In this part, we total up the sales (Total_Retail_Price) and profit (Profit) from each order into a single observation for each order.

1) Add a Data step that creates the work.orders_fin_tot data set from the work.orders_order_item_fin data set. This new data set should only contain one observation per Order_ID. These observations will total up the Total_Retail_Price and Profit variables for each order. Call the new variables TotalSales and TotalProfit. Use the SUM statement to total up the sales and profits. You will need to use the First.Order_ID variable to zero out the new variables for each order and the Last.Order_ID variable to output the observation to the work.orders_fin_tot data set. Drop the Total_Retail_Price and Profit variables from the new data set.

2) Add a Proc step that displays the first 10 observations of the work.orders_fin_tot data set.

Part F:

1) Add a Data step that creates the work.orders_fin_qtr_tot data set from the work.orders_fin_tot data set. This new data set should contain new variables for quarterly sales and profit. Use two arrays to create the new variables: QtrSales1- QtrSales4 and QtrProfit1- QtrProfit4. These represent total sales and total profit for the quarter (1-4). Use the quarter number of the year in which the order was placed to index into the correct variable to add either the TotalSales or TotalProfit to the new appropriate variable.

2) Add a Proc step that displays the first 10 observations of the work.orders_fin_qtr_tot data set.

3) Add a Proc step that displays in a table just the mean and sum for each of the following variables from the work.orders_fin_qtr_tot data set using 2 decimal places: TotalSales, TotalProfit, QtrSales1- QtrSales4, and QtrProfit1- QtrProfit4.

Part G:

Note: The Order_Type variable in the orion.orders data set has the following meaning:

1: Retail order

2: Catalog order

3: Internet order

1) Add a Data step that creates three new data sets:

? work.retail_orders_fin

? work.catalog_orders_fin

? work.internet_orders_fin

from the work.orders_fin_tot data set. Use a SELECT / WHEN block to distribute the observations to the appropriate data set based on their Order_Type variable.

2) Add a Proc step that displays the first 10 observations of the work.retail_orders_fin data set.

3) Add a Proc step that displays in a table just the mean and sum for each of the following variables from the work.retail_orders_fin data set using 2 decimal places: TotalSales and TotalProfit

4) Add a Proc step that displays the first 10 observations of the work.catalog_orders_fin data set.

5) Add a Proc step that displays in a table just the mean and sum for each of the following variables from the work.catalog_orders_fin data set using 2 decimal places: TotalSales, TotalProfit and Ship_Time

6) Add a Proc step that displays the first 10 observations of the work.internet_orders_fin data set.

7) Add a Proc step that displays in a table just the mean and sum for each of the following variables from the work.internet_orders_fin data set using 2 decimal places: TotalSales, TotalProfit and Ship_Time