Topics about : Reading Raw Data Files; Data Transformaton Part I

see the file for detail.

Part I: Reading Raw Data Files

1. 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	Numeric $100,000
Country	73	2	Character ‘AU’ or ‘US’
Birth Date	76	10	Numeric mm/dd/yyyy
Hire Date	87	10	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 work.AU_trainees (21 Total Observations)

Australian Trainees

Employee_ Hire_

ID Last_Name Job_Title Salary Date

120123 Hotstone Sales Rep. I 26190 18901

120124 Daymond Sales Rep. I 26480 18687

120130 Lyon Sales Rep. I 26955 18748

120131 Surawski Sales Rep. I 26910 18628

120136 Leyden Sales Rep. I 26605 18659

Partial work.US_trainees (42 Total Observations)

US Trainees

Employee_ Hire_

ID Last_Name Job_Title Salary Date

121023 Fuller Sales Rep. I 26010 18748

121028 Smades Sales Rep. I 26585 18932

121029 Mcelwee Sales Rep. I 27225 18962

121030 Areu Sales Rep. I 26745 18659

121036 Mesley Sales Rep. I 25965 18901

2. Working with Mixed Record Types

The raw data file sales3.dat has employee information for the Australian and U.S. sales 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	Numeric dd/mm/yyyy
Hire Date	24	10	Numeric dd/mm/yyyy

Line 2 layout for U.S. employees

Field Description	Starting Column	Length of Field	Data Type
Salary	1	8	Numeric $100,000
Country	10	2	Character
Birth Date	13	10	Numeric mm/dd/yyyy
Hire Date	24	10	Numeric mm/dd/yyyy

a. 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.

b. Print both of the data sets with appropriate titles.

Partial work. AU_sales (63 Total Observations)

Australian Sales Staff

Employee_ Hire_

ID Last_Name Job_Title Salary Date

120102 Zhou Sales Manager 108255 12205

120103 Dawes Sales Manager 87975 6575

120121 Elvish Sales Rep. II 26600 6575

120122 Ngan Sales Rep. II 27475 8217

120123 Hotstone Sales Rep. I 26190 18901

120124 Daymond Sales Rep. I 26480 18687

120125 Hofmeister Sales Rep. IV 32040 8460

Partial work. US_sales (102 Total Observations)

US Sales Staff

Employee_ Hire_

ID Last_Name Job_Title Salary Date

120261 Highpoint Chief Sales Officer 243190 11535

121018 Magolan Sales Rep. II 27560 6575

121019 Desanctis Sales Rep. IV 31320 17684

121020 Ridley Sales Rep. IV 31750 16922

121021 Farren Sales Rep. IV 32985 13939

121022 Stevens Sales Rep. IV 32210 16833

121023 Fuller Sales Rep. I 26010 18748

121024 Westlund Sales Rep. II 26600 17653