Part I: Combining data sets

1. Concatenating Like-Structured Data Sets

a. Write and submit a DATA step to concatenate orion.mnth7_2011, orion.mnth8_2011, and orion.mnth9_2011 to create a new data set, work.thirdqtr.

How many observations in work.thirdqtr are from orion.mnth7_2011?

How many observations in work.thirdqtr are from orion.mnth8_2011?

How many observations in work.thirdqtr are from orion.mnth9_2011?

b. Write a PROC PRINT step to create the report below. The results should contain 32 observations.

Partial PROC PRINT Output

Order_ Order_ Delivery_

Obs Order_ID Type Employee_ID Customer_ID Date Date

1 1242691897 2 99999999 90 02JUL2011 04JUL2011

2 1242736731 1 121107 10 07JUL2011 07JUL2011

3 1242773202 3 99999999 24 11JUL2011 14JUL2011

4 1242782701 3 99999999 27 12JUL2011 17JUL2011

5 1242827683 1 121105 10 17JUL2011 17JUL2011

2. Concatenating Unlike-Structured Data Sets

Open p110e02. Submit the two PROC CONTENTS steps or explore the data sets interactively to compare the variables in the two data sets. What are the names of the two variables that are different in the two data sets?

orion.sales	orion.nonsales

a. Add a DATA step after the PROC CONTENTS steps to concatenate orion.sales and orion.nonsales to create a new data set, work.allemployees.

Use a RENAME= data set option to change the names of the different variables in orion.nonsales.

The new data set should include only Employee_ID, First_Name, Last_Name, Job_Title,
and Salary.

b. Add a PROC PRINT step to create the report below. The results should contain 400 observations.

Partial PROC PRINT Output

First_

Obs Employee_ID Name Last_Name Salary Job_Title

1 120102 Tom Zhou 108255 Sales Manager

2 120103 Wilson Dawes 87975 Sales Manager

3 120121 Irenie Elvish 26600 Sales Rep. II

4 120122 Christina Ngan 27475 Sales Rep. II

5 120123 Kimiko Hotstone 26190 Sales Rep. I

3. Merging Two Sorted Data Sets in a One-to-Many Merge

a. Retrieve the starter program p110e04.

b. Submit the two PROC CONTENTS steps or explore the data sets interactively to determine
the common variable among the two data sets.

c. Add a DATA step after the two PROC CONTENTS steps to merge orion.orders and orion.order_item by the common variable to create a new data set, work.allorders. A sort
is not required because the data sets are already sorted by the common variable.

d. Submit the program and confirm that work.allorders was created with 732 observations
and 12 variables.

e. Add a statement to subset the variables. The new data set should contain six variables: Order_ID, Order_Item_Num, Order_Type, Order_Date, Quantity, and Total_Retail_Price.

f. Write a PROC PRINT step to create the report below. Include only those observations with a value for Order_Date in the fourth quarter of 2011. The results should contain 35 observations.

Order_ Order_ Order_ Total_Retail_

Order_ID Type Date Item_Num Quantity Price

1243515588 1 01OCT2011 1 1 $251.80

1243515588 1 01OCT2011 2 1 $114.20

1243568955 1 07OCT2011 1 1 $172.50

1243643970 1 16OCT2011 1 1 $101.50

1243644877 3 16OCT2011 1 1 $14.60

4. Merging Using the IN= Option

a. Retrieve the starter program p110e07. Add a DATA step after the PROC SORT step to merge work.product and orion.supplier by Supplier_ID to create a new data set, work.prodsup.

b. Submit the program and confirm that work.prodsup was created with 556 observations
and 10 variables.

c. Modify the DATA step to output only those observations that are in work.product but not orion.supplier. A subsetting IF statement that references IN= variables listed in the MERGE statement must be added.

d. Submit the program and confirm that work.prodsup was created with 75 observations
and 10 variables.

e. Submit the PROC PRINT step to create the report below. The results should contain 75 observations.

? Supplier information is missing in the output.

Supplier_

Obs Product_ID Product_Name Supplier_ID Name

1 210000000000 Children .

2 210100000000 Children Outdoors .

3 210100100000 Outdoor things, Kids .

4 210200000000 Children Sports .

5 210200100000 A-Team, Kids .