Jedi SAS Tricks: These aren't the droids... Episode 2

0

We started out in Episode 1 cleaning and standardizing data from the Academy.  As you recall, Mr. Vader apparently had a attitude when making his entry in the database.  The original data looked like this:

Data table with garbage punctuation in Names, various formats for phone numbers and height and weight data entered in mixed metric and imperial units.

Data set MixedUp

You can find the code for creating the original data set back in Episode 1. There, we demonstrated the power of the COMPRESS function which, when combined with a nice little picture format, did the initial data cleanup:

Data set: CleanedUp1

In this episode, we'll finish cleaning up the last problem: Mr Vader insisted on entering his height and weigh data in Imperial vs. metric units (our standard).  Once again, we'll leverage the power of the COMPRESS function, but two additional functions will help make our code succinct and efficient: FIND and IFN.

The FIND function searches  for a specific substring of characters within a character string, and can do case-sensitive searches or ignore case.  It returns a 0 (boolean false) if the text is not found, or a number indicating the position in the larger string where the text of the smaller substring starts (boolean true).

The IFN function returns a numeric value based on whether an expression is true, false, or missing. The first argument is the expression to evaluate for true or false, the second is the expression producing the value to return if true, the third the expression producing the value to return if false.

To fix the height data, we will COMPRESS out everything except the digits, just like we did for the phone number.  But before storing the value in the Height variable, we'll test to see if it was entered in inches by trying to FIND 'in' in the original text value.  If found, we'll multiply the value by 2.54 (there are 2.54 cm/in) before storing it in the Height variable.  We'll use a similar process for weight. If the weight was given in pounds, we'll divide the value by 2.2046 before storing it in the Weight variable.

Here is the syntax to determine if the text variable ht contains the text 'in' using the FIND function:

   find(ht,'in','i')

Now, when we process Mr. Vader's height information, it DOES contain the string 'in' so our code must use conditional processing to decide to do the math. We'll use the IFN function for this.

Here is the syntax which conditionally determines a multiplier value based on the results of the FIND function:

      IFN(find(ht,'in','i'),2.54,1)

Using similar syntax to determine the divisor for weight, and adding new picture formats for our height and weight values, we get this final "cleanup code":

proc format;
   picture phone (default=13) low-high='000)000-0000' (prefix='(');
   picture kg (default=7) low-high='0000 kg' ;
   picture cm (default=7) low-high='0000 cm' ;
run;
 
data CleanedUp;
   LENGTH Name $ 20 Phone Height Weight 8;
   LABEL Name='Student Name'
         Phone='Primary Phone Number'
         Height='Height'
         Weight='Weight';
   keep Name--Weight;
   format Phone phone. height weight 5.;
   set MixedUp;
   /* Compress to keep only alpha characters */
   Name=Compress(Name,'. ','ka');
   /* Compress to keep only digits. */
   Phone=input(compress(ph,,'kd'),15.);
   /* Compress to keep digits.  If originally in inches, convert value */
   Height=input(compress(ht,,'kd'),15.)*IFN(find(ht,'in','i'),2.54,1);
   /* Compress to keep digits.  If originally in lbs, convert value */
   Weight=input(compress(wt,,'kd'),15.)/ifn(find(Wt,'lbs','i'),2.20462262,1);
   format Weight kg. Height cm.;
run;

And that did the trick! Our final, cleaned up data looks great:

Final view of the data set, with all of the garbage characters removed and all units converted to metric

Data set CleanedUP

You may be wondering about my recent fascination with data cleaning... did I mention that I also recently started teaching Ron Cody's "Data Cleaning Techniques" class? I've been preparing to teach it for the first time in March - and thought I'd share a few of the things I'd learned with you.

That's all for this episode. Until the next time, may the SAS be with you!
Mark

Tags:
Share.

About Author

SAS Jedi

Principal Technical Training Consultant

Mark Jordan (a.k.a. SAS Jedi) grew up in northeast Brazil as the son of Baptist missionaries. He served 20 years as a US Navy submariner, pursuing his passion for programming as a hobby. Upon retiring from the Navy in 1994, he turned his hobby into a dream job and has been a SAS programmer ever since. Mark writes and teaches a broad spectrum of SAS Foundation programming classes, and is proud to announce his first book, "Mastering the SAS® DS2 Procedure: Advanced Data Wrangling Techniques". When he isn’t writing, teaching, or posting “Jedi SAS Tricks” here on the SAS Learning Post, Mark enjoys playing with his grand and great-grandchildren, hanging out at the beach and reading science fiction novels. His secret obsession is flying toys – kites, rockets, drones – and though he usually tries to convince Lori that they are for the grandkids, she isn't buying it. Mark lives in Toano, VA with his wife, Lori, and their cat, the amazing Tiger Man. To connect with Mark, check out his SAS Press Author page, follow him on Twitter @SASJedi or connect on Facebook or LinkedIn.

Leave A Reply

Back to Top