506Homework STAT 7
For most problems you will need to access the data in the PRG2 folder. Use the libname statement we learned to load
this each time you work on your assignments calling the library orion. I tried to bold the parts where I expect you to
actually show me something in your homework solutions if it is not obvious.
Do the following problems.
1. Creating Accumulating Totals with Conditional Logic
The data set orion.order_fact contains a group of orders across several years, sorted
by Order_Date.
Partial Listing of orion.order_fact (617 Total Observations, 12 Total Variables)
Order_
Order_ID
1230058123
1230080101
1230106883
1230147441
1230315085
Type
1
2
2
1
1
Date
11JAN2003
15JAN2003
20JAN2003
28JAN2003
27FEB2003
Order_
Quantity
1
1
1
2
3
a. Orion Star would like to analyze 2005 data by creating accumulating totals for the number
of items sold from retail, catalog, and Internet channels.
The value of Order_Type indicates whether the sale was retail (=1), catalog (=2),
or Internet (=3).
Create a data set named work.typetotals with accumulating totals for TotalRetail,
TotalCatalog, and TotalInternet, as described above.
The variable Quantity contains the number of items sold for each order.
For testing your program in this step, read only the first 10 observations that satisfy the WHERE statement.
Remember to process only those rows where Order_Date occurs in 2005.
b. Continue testing your program by printing the results from step a. Print all the variables and check to make sure
that the program is correctly calculating values for the accumulating totals.
PROC PRINT Output
Obs
1
2
3
4
5
Customer_ID
195
36
183
16
16
Obs Quantity
1
2
2
4
3
1
4
1
5
1
Employee_ID
120150
99999999
120121
99999999
99999999
Total_Retail_
Price
$193.40
$525.20
$16.00
$115.00
$138.70
Street_ID
1600101663
9260128237
1600100760
3940105865
3940105865
Order_
Date
02JAN2005
11JAN2005
12JAN2005
12JAN2005
17JAN2005
CostPrice_
Per_Unit
$48.45
$58.55
$6.35
$52.40
$62.50
Delivery_
Date
02JAN2005
14JAN2005
12JAN2005
14JAN2005
19JAN2005
Discount
.
.
.
.
.
Order_ID
1234437760
1234534069
1234537441
1234538390
1234588648
Total
Retail
2
2
3
3
3
Order_
Type
1
3
1
2
2
Total
Catalog
0
0
0
1
2
Product_ID
230100600028
240800100026
240100200001
220200300015
230100500101
Total
Internet
0
4
4
4
4
c. When the results from steps a and b are correct, do the following:
Modify the program to read all observations satisfying the WHERE statement.
Keep only the variables Order_Date, Order_ID, TotalRetail, TotalCatalog, and
TotalInternet.
Print your results with an appropriate title.
Show the final code and the output showing the first ten observations from the proc print.
2. Summarizing Data Using the DATA Step
The data set orion.order_summary contains information about sales in a particular year for each customer,
separated by month. For a given customer, there might be some months that he did not place an order.
Partial Listing of orion.order_summary (101 Total Observations)
Customer_ID
Month
5
5
5
5
10
10
Order_
Sale_Amt
5
6
9
12
3
4
478.00
126.80
52.50
33.80
32.60
250.80
a. Sort the input data set, orion.order_summary, by Customer_ID. Use the OUT= option to avoid
overwriting the original data set. Name the output data set work.sumsort.
b. Create a new data set showing a total sales value for each customer.
Name the new data set work.customers.
Name the new variable Total_Sales. This variable contains the total of sales across all months for each
customer.
c. Print your result.
Display Total_Sales with a DOLLAR11.2 format.
Add an appropriate title.
Partial PROC PRINT Output (37 Total Observations)
Total Sales to each Customer
Obs
Customer_ID
Total_Sales
1
2
3
4
5
5
10
11
12
18
$691.10
$3,479.09
$78.20
$253.20
$29.40
Show the final code and the output showing the first ten observations from the proc print.
3. Summarizing and Grouping Data Using the DATA Step
The data set orion.order_qtrsum contains information about sales in a particular year for
each customer, separated by month.
For a given customer, there might be some months (and quarters) that the customer did not place an order.
The variable Order_Qtr contains the appropriate quarter.
Partial Listing of orion.order_qtrsum (101 Total Observations)
Customer_ID
69
70187
10
70079
70165
92
41
171
41
69
49
Order_
Qtr
Order_
Month
Sale_Amt
4
4
2
4
3
1
3
2
2
3
4
10
11
6
10
7
3
8
4
5
9
12
3.2
8.2
12.2
14.6
16.6
16.9
17.6
19.1
19.9
23.5
24.8
The data set is not sorted by Customer_ID and Order_Qtr.
a. Create a data set named work.qtrcustomers that summarizes sales based on customer and quarter.
The variable Total_Sales should contain the total sales for each quarter within each Customer_ID value.
Create a variable named Num_Months that counts the total months within each quarter that the customer had
an order.
b. Print your results.
Display Total_Sales with a DOLLAR11.2 format.
Add an appropriate title.
Partial PROC PRINT Output (74 Total Observations)
Total Sales to each Customer for each Quarter
Obs
Customer_ID
1
2
3
4
5
Order_
Qtr
5
5
5
10
10
Total_Sales
2
3
4
1
2
Num_
Months
$604.80
$52.50
$33.80
$32.60
$342.80
2
1
1
1
3
Show the final code and the output showing first the ten observations from the proc print.
4. Using Formatted Input and the Subsetting IF Statement
The raw data file sales1.dat has employee information for the Australian and U.S. sales staff. The record layout is
shown in the table below:
Layout for sales1.dat
Field
Description
Starting
Column
Length of
Field
Data Type
Employee ID
1
6
Numeric
First Name
8
12
Character
Last Name
21
18
Character
Gender
40
1
Character
Job Title
43
20
Character
Salary
64
8
Country
73
2
Birth Date
76
10
Hire Date
87
10
Numeric
$100,000
Character
'AU' or 'US'
Numeric
mm/dd/yyyy
Numeric
mm/dd/yyyy
a. Create two SAS data sets from the raw data file, and base them on the country of the trainee.
Name the data sets US_trainees and AU_trainees. For this exercise, a trainee is anyone that has the job
title of Sales Rep. I
Each data set should contain the fields indicated by arrows in the layout table.
Write only U.S. trainees to the US_trainees data set and only Australian trainees to the AU_trainees
data set. Do not keep the Country variable in the output data sets.
b. Print both of the data sets with appropriate titles.
Partial PROC PRINT Output for AU_trainees (21 Total Observations)
Australian Trainees
Employee_
ID
Last_Name
120123
120124
120130
Job_Title
Hotstone
Daymond
Lyon
Sales Rep. I
Sales Rep. I
Sales Rep. I
Salary
Hire_
Date
26190
26480
26955
17440
17226
17287
Partial PROC PRINT Output for US_trainees (42 Total Observations)
US Trainees
Employee_
ID
121023
121028
121029
Last_Name
Fuller
Smades
Mcelwee
Job_Title
Salary
Hire_
Date
26010
26585
27225
17287
17471
17501
Sales Rep. I
Sales Rep. I
Sales Rep. I
Show the final code and the output showing the first ten observations from both proc prints.
5. Reading Multiple Input Records per Observation
The raw data file sales2.dat has employee information for the Australian and U.S. sales staff.
Information for each employee is in three lines of raw data.
The record layouts are shown below.
Line 1 layout
Field
Description
Starting
Column
Length of
Field
Data Type
Employee ID
1
6
Numeric
First Name
8
12
Character
Last Name
21
18
Character
Line 2 layout
Field
Description
Starting
Column
Length of
Field
Job Title
1
20
Hire Date
22
10
Salary
33
8
Data Type
Character
Numeric
mm/dd/yyyy
Numeric
for example, $100,000
Line 3 layout
Field
Description
Starting
Column
Length of
Field
Data Type
Gender
1
1
Character
Birth Date
3
10
Numeric
mm/dd/yyyy
Country
14
2
Character
c. Create a new SAS data set named sales_staff2 that contains the fields indicated by arrows in the layout
table.
d. Print sales_staff2 and add an appropriate title.
Partial PROC PRINT Output (165 Total Observations)
Australian and US Sales Staff
Employee_
ID
120102
120103
120121
Last_Name
Zhou
Dawes
Elvish
Job_Title
Hire_
Date
Salary
Sales Manager
Sales Manager
Sales Rep. II
10744
5114
5114
108255
87975
26600
Show the final code and the output showing the first ten observations from the proc print.
6. Working with Mixed Record Types
The raw data file sales3.dat has employee information for the Australian and U.S. Ssales staff.
Information for each employee is in two lines of raw data.
The record layouts are shown below.
Line 1 layout
Field
Description
Starting
Column
Length of
Field
Data Type
Employee ID
1
6
Numeric
First Name
8
12
Character
Last Name
21
18
Character
Gender
40
1
Character
Job Title
43
20
Character
Line 2 layout for Australian employees
Field
Description
Starting
Column
Length of
Field
Data Type
Salary
1
8
Numeric
$100,000
Country
10
2
Character
Birth Date
13
10
Hire Date
24
10
Numeric
dd/mm/yyyy
Numeric
dd/mm/yyyy
Line 2 layout for U.S. employees
Field
Description
Startin
g
Column
Length of
Field
Data Type
Salary
1
8
Numeric
$100,000
Country
10
2
Character
Birth Date
13
10
Hire Date
24
10
Numeric
mm/dd/yyyy
Numeric
mm/dd/yyyy
e. Create two new SAS data sets, US_sales and AU_sales, that contain the fields indicated
by arrows in the layout table. Write only U.S. employees to the US_sales data set and only Australian
employees to the AU_sales data set. Do not include the Country variable in
the output data sets.
The salary and hire date values are different for Australian and U.S. employees.
Be sure to use the correct informats in each INPUT statement.
f. Print both of the data sets with appropriate titles.
Partial PROC PRINT Output for AU_sales (63 Total Observations)
Australian Sales Staff
Employee_
ID
120102
120103
120121
120122
120123
Last_Name
Zhou
Dawes
Elvish
Ngan
Hotstone
Job_Title
Sales Manager
Sales Manager
Sales Rep. II
Sales Rep. II
Sales Rep. I
Salary
108255
87975
26600
27475
26190
Hire_
Date
10744
5114
5114
6756
17440
Partial PROC PRINT Output for US_sales (102 Total Observations)
US Sales Staff
Employee_
ID
120261
121018
121019
121020
121021
Last_Name
Highpoint
Magolan
Desanctis
Ridley
Farren
Job_Title
Chief Sales Officer
Sales Rep. II
Sales Rep. IV
Sales Rep. IV
Sales Rep. IV
Salary
243190
27560
31320
31750
32985
Hire_
Date
10074
5114
16223
15461
12478
Show the final code and the output showing the first ten observations from both proc prints.