7 tips for creating cleaner data sets

Although she’s an analyst, Anca Tilea estimates that she spends 80% of her time cleaning data. Tilea and co-author Deanna Chyn shared seven of their favorite methods for checking, cleaning and restructuring data.

Attendees at MWSUG 2013 got a bonus tip: Ask SAS peers in one of the SAS Support Communities. Anca told the audience that the communities are a “huge resource” for her now. Topics range from basic to really complex, and the discussion is always good.

The description of each tip Data Cleaning 101: An Analyst’s Perspective suggests processing challenges where the technique is most useful. There are side-by-side code examples that show less effective, but workable coding methods alongside the following:

CALL SYMPUT saves values into macro variables that can later be recalled

PROC SQL SELECT INTO to avoid coding errors when listing variable names

PROC TRANSPOSE for rearranging data before performing calculations on columns

%MACRO %DO 1 %TO n or macro arrays to handle repetitive reads

PROC SQL JOIN for working with date ranges

three SAS character functions for subsetting an existing SAS dataset based on some specific rule

IF-ELSE statements that avoid unnecessary processing time

You can find this paper and more at the MWSUG 2013 Proceedings.

7 tips for creating cleaner data sets was published on SAS Users.