Data Cleaning in SAS: Tips and Techniques

Introduction

Data cleaning is a critical step in the data preparation process. Raw data is often messy, incomplete, or inconsistent, and cleaning it ensures accurate and reliable analysis. SAS offers a wide range of tools and functions that make data cleaning efficient and effective.

In this post, you’ll learn essential data cleaning techniques in SAS, including handling missing values, removing duplicates, formatting variables, and more.

---

1. Identifying and Handling Missing Values

Missing data can lead to biased results. SAS represents missing numeric values as . and character missing values as a blank space ('').

Check for missing values:

PROC MEANS DATA=your_dataset N NMISS;
RUN;

Replace missing values:

DATA cleaned_data;
  SET your_dataset;
  IF Age = . THEN Age = 0;
  IF Name = '' THEN Name = 'Unknown';
RUN;

---

2. Removing Duplicate Records

Duplicate data can affect the accuracy of your results.

Identify duplicates:

PROC SORT DATA=your_dataset OUT=check_dups NODUPKEY;
  BY ID;
RUN;

Remove complete duplicates:

PROC SORT DATA=your_dataset NODUPRECS OUT=cleaned_data;
RUN;

---

3. Standardizing Text Variables

Standardizing case and removing unwanted spaces improves consistency.

Use SAS functions:

DATA standardized;
  SET your_dataset;
  Name_clean = STRIP(UPCASE(Name));
RUN;

• STRIP() removes leading and trailing spaces
• UPCASE(), LOWCASE() or PROPCASE() standardize text case

---

4. Filtering Out Invalid Values

Sometimes variables contain invalid or out-of-range data.

Example: Remove ages less than 0 or more than 120

DATA cleaned_data;
  SET your_dataset;
  IF 0 <= Age <= 120;
RUN;

---

5. Converting Data Types (INPUT and PUT)

Mismatch between numeric and character types can cause issues.

Convert character to numeric:

Age_num = INPUT(Age_char, 8.);

Convert numeric to character:

Age_char = PUT(Age_num, 8.);

---

6. Replacing Values with IF or ARRAY Logic

You can recode or transform values using conditional logic.

Example:

DATA recoded;
  SET your_dataset;
  IF Gender = 'M' THEN Gender = 'Male';
  ELSE IF Gender = 'F' THEN Gender = 'Female';
RUN;

---

7. Handling Outliers

Use PROC UNIVARIATE to detect outliers in numeric variables.

PROC UNIVARIATE DATA=your_dataset;
  VAR Salary;
RUN;

Then, you can treat outliers by capping, removing, or flagging them.

---

8. Validating Cleaned Data

Use PROC FREQ or PROC MEANS to validate the cleaned dataset.

PROC FREQ DATA=cleaned_data;
  TABLES Gender Age_Group;
RUN;

PROC MEANS DATA=cleaned_data;
  VAR Salary Age;
RUN;

---

Best Practices for Data Cleaning in SAS

• Always keep a backup of your raw data
• Document every cleaning step
• Use descriptive variable names (e.g., Name_clean, Age_flag)
• Combine steps using macros for reusable workflows

---

Conclusion

Clean data is the foundation of trustworthy analysis. Using SAS, you can efficiently handle missing values, fix data quality issues, and standardize your datasets. By mastering these data cleaning techniques, you’re well on your way to becoming a proficient SAS programmer and data analyst.

---

Tags: