SAS数据清洗技巧：发现、修复与验证的实用方法（第2版）

需积分: 14 46 浏览量更新于2024-07-18 收藏 925KB PDF 举报

"Cody's Data Cleaning Techniques Using SAS, Second Edition" 是一本由Ron Cody撰写的指南，专为IT专业人士提供在使用SAS软件进行数据清洗的实用方法和技巧。该书详细讲解了如何处理和检测不同类型数据集中的问题，包括字符变量和数值变量的异常值、缺失值、重复记录、日期格式错误以及跨多个文件的数据一致性检查。书中涵盖了以下几个关键知识点： 1. **检查字符变量值**：介绍了使用`PROC FREQ`列出不同值的方法，并展示了如何使用`VERIFY`, `TRIM`, `MISSING`, 和 `NOTDIGIT` 函数来识别无效数据。此外，还讨论了如何通过`PROC PRINT`配合`WHERE`语句来筛选出有问题的值，并探讨了格式化和informats在清理字符变量时的作用。 2. **检查数值变量**：这部分着重于查找数值变量中的异常值，如使用`PROC MEANS`, `PROC TABULATE`, 和 `PROC UNIVARIATE`找出离群值。书中提供了使用ODS SELECT来显示极端值，以及如何通过`PROC UNIVARIATE`选项和`PROC RANK`找出最高和最低值以及百分比分布。 3. **范围检查和异常值检测**：讲解了如何利用统计方法，如标准差、四分位距等来确定数据的合理范围，并提出基于修剪均值和标准差的方法来检测异常值。还提供了一个基于修剪统计的宏和使用`TRIM`选项在`PROC UNIVARIATE`和ODS中的应用。 4. **处理缺失值**：介绍了检查缺失值的方法，包括查看SAS日志、使用`PROC MEANS`和`PROC FREQ`计数缺失值，以及使用数据步来识别并计数缺失值。 5. **日期处理**：针对日期范围检查和非标准格式的日期处理提供了具体步骤，包括如何在日历不完整的情况下创建SAS日期，以及处理已知无效日期的策略。 6. **删除重复和多观察记录**：指导读者如何使用`PROC SORT`消除重复项，以及使用数据步和SQL来查找特定数量的观测记录。 7. **处理多个文件**：介绍了如何检查两个或更多文件中的ID一致性，以及更复杂的多文件规则和日期顺序验证。 8. **数据比较和错误修正**：讲解了如何使用`PROC COMPARE`进行数据集之间的简单比较，以及处理不同数据集大小和变量差异的情况。还提到了硬编码修正、命名输入和UPDATE语句的应用。这本指南对于那些希望提升数据质量控制和管理能力的IT专业人士来说是一本实用的工具书，它不仅提供了理论知识，还给出了实际操作的步骤，使读者能够有效地运用SAS进行数据清洗工作。

Chapter 1 Checking Values of Character Variables 9

If you want to allow lowercase M's and F's as valid values, you can add the single line

Gender = upcase(Gender);

immediately before the line that checks for invalid gender codes. As you can probably guess, the

UPCASE function changes all lowercase letters to uppercase letters.

If you know from the start that you may have both upper- and lowercase values in your raw data

file, you could use the $UPCASE informat to convert all lowercase values to uppercase. For

example, to read all Gender values in uppercase, you could use:

@4 Gender $upcase1.

to replace the line that reads Gender values in Program 1-1.

A statement similar to the gender checking statement is used to test the adverse events.

There are so many valid values for Dx (any numeral from 1 to 999) that the approach you used

for Gender and AE would be inefficient (and wear you out typing) if you used it to check for

invalid Dx codes. The VERIFY function is one of the many possible ways you can check to see if

there is a value other than the numerals 0 to 9 as a Dx value. The next section describes the

VERIFY function along with several other functions.

Describing the VERIFY, TRIM, MISSING, and NOTDIGIT Functions

The verify function takes the form:

verify(character_variable,verify_string)

where verify_string is a character value (either the name of a character variable or a series of

values placed in single or double quotes). The VERIFY function returns the first position in the

character_variable that contains a character that is not in the verify_string. If the

character_variable does not contain any invalid values, the VERIFY function returns a 0. To

make this clearer, let's look at some examples of the VERIFY function.

10 Cody’s Data Cleaning Techniques Using SAS, Second Edition

Suppose you have a variable called ID that is stored in five bytes and is supposed to contain only

the letters X, Y, Z, and digits 0 through 5. For example, valid values for ID would be X1234 or

34Z5X. You could use the VERIFY function to see if the variable ID contained any characters

other than X, Y, Z and the digits 0 through 5 like this:

Position = verify(ID,'XYZ012345');

Suppose you had an ID value of X12B44. The value of Position in the line above would be 4, the

position of the first invalid character in ID (the letter B). If no invalid characters are found, the

VERIFY function returns a 0. Therefore, you can write an expression like the following to list

invalid values of ID:

if verify(ID,'XYZ012345') then put "Invalid value of ID:" ID;

This may look strange to you. You might prefer the statement:

if verify(ID,'XYZ012345') gt 0 then put "Invalid value of ID:" ID;

However, these two statements are equivalent. Any numerical value in SAS other than 0 or

missing is considered TRUE. You usually think of true and false values as 1 or 0—and that is

what SAS returns to you when it evaluates an expression. However, it is often convenient to use

values other than 1 to represent TRUE. When SAS evaluates the VERIFY function in either of

the two statements above, it returns a 4 (the position of the first invalid character in the ID). Since

4 is neither 0 or missing, SAS interprets it as TRUE and the PUT statement is executed.

There is one more potential problem when using the VERIFY function. Suppose you had an ID

equal to 'X123'. What would the expression

verify(ID,'XYZ012345')

return? You might think the answer is 0 since you only see valid characters in the ID (X, 1, 2, and

3). However, the expression above returns a 5! Why? Because that is the position of the first

trailing blank. Since ID is stored in 5 bytes, any ID with fewer than 5 characters will contain

trailing blanks—and blanks, even though they are sometimes hard to see, are still considered

characters to be tested by the VERIFY function.

剩余243页未读，继续阅读

yanghefeng22

粉丝: 0
资源: 13

SAS数据清洗技巧：发现、修复与验证的实用方法（第2版）

Cody's data cleaning techniques using SAS 2nd Edition

Cody's Collection of popular SAS programming tasks

Cody's Data Cleaning Techniques Using SAS (Second Edtion).pdf

[{'_id': ObjectId('6669ad4fc455f95c2a9f308d'), 'name': 'cody', 'age': 24, 'phone': '15724137016'}] 这样data无法返回

matlab Cody Quote Doubler

matlab cody whether

delete from 语句

matlab cody

cobaltstrike远控木马

No function matches the given name and argument types. You might need to add explicit type casts.

最新资源