SAS day 56: ANYALPHA
Background Story:
Once we had an excel sheet with an extreme variety of date formats that don’t make sense. Is there a method to clean the data so they will be meaningful dates?
Raw Data:
Solutions Ideas:
After observing the data structure, we notice it either starts with a letter or number. Therefore, we will apply the miraculous ANYALPHA function to detect the first string is a letter or a number.

tangjiao990 (CC0), Pixabay
Basic Syntax:
ANYALPHA(string, i) : Search a character string for the first alphabetic character starts at a specific ith position; if no specific position is assigned then it searches from the beginning.
Solution Steps:
- if the first string is a letter then set the newdate to missing.
-
if the first string is a number then set the newdate using the input function
Note, we need to minus a constant “-21916” if the date range is absurd.
SAS Code:
data sample1; format newdate date9.; set sample; if anyalpha(date)^=1 then do; if anyalpha(scan(Date,2,'-'))=1 then newdate=input(Date,date9.); else if length(Date)<9 then newdate=input(Date,8.)-21916; end; if anyalpha(date)=1 then alpha_flag="Y"; run;
Output:
Now we can see the pretty newdate we come up with.
Summary:
While i got very frustrating to work with the raw data that doesn’t make sense, I have learned so much in these situations. I can view it as a challenge or an opportunity.
Thanks to my colleague Yajnes that always generously sharing all the SAS tricks with me!
Happy Studying!