lecture07 清洗和增加数据.ppt

Lecture 7 Cleansing and Augmenting the Data 7、清洗和增加数据 Section 7.1 Cleansing and Augmenting the Data Objectives Modify the data using the functions UPCASE COMPBL TRANWRD. Scenario The secretary at Statistics Department maintains some faculty information. The data set contains information about all faculty in Fall 2001. UPCASE Function The UPCASE function converts all of the letters in the data value into uppercase. COMPBL Function The COMPBL function compresses multiple consecutive blanks in a data value into one blank. Since the length of a variable is set at compilation, the resulting data value is padded with blanks. Example: TRANWRD Function The TRANWRD function replaces all occurrences of a pattern of characters in a data value with another pattern of characters. Example: Index(name,’a’) use the index function to find the cases with the letter a in the name Return an numeric. Cleansing Existing Data This demonstration illustrates changing Country to be always uppercase for USA and only the first letter be upper case for other counties removing multiple blanks from a last name Section 7.2 Creating New Character Variables Objectives Modify character data values. Specifically, use the Scenario University has two email systems “pegasus” and “mail”. The e-mail address structures are in the form of FirstinitialLastname@ Or FirstinitialLastname@ Refresher Quiz! 1. What are the two phases of the DATA step? Refresher Quiz! 1. What are the two phases of the DATA step? SUBSTR Function The SUBSTR function extracts a portion of the character data value based on how many characters are designated for retrieval. Retrieve First Initial The first step is to retrieve the initial letter of an employee’s first name. first_initial = substr(emp_firstname,1,1); Retrieve Middle Initial The second step is to retrieve the middle initial. Problems: Not all middle initials are in the same location, so you cannot use the SUBSTR function. Not all employees have a middle initia