07-18-2017 10:22 AM
I am attempting to standardize a variable in a data set that is intented to record weeks of pregnancy. The data was entered via multiple sources who were not consistent. The variable is as a combination of weeks (integers/decimals/words) and date format, MM/DD/YYYY. These observations were all read as a character variable as well. I am unsure how to proceed since the data was not entered consistently in the same format. Examples of the different forms used to communicate the variable which I need to standardize over 18,000 observations are as follows:
07-18-2017 11:10 AM
How can you tell if a date is DDMMYY or MMDDYY?
Unfortunately there is no magic fix for this, except for cleaning your data. But you can do a few at a time. For example, I would search for the word WEEK or WK first and try and separate them into different groups and then clean them up. You may be tempted to clean them up manually, but remember you want a trace of what you did.
07-18-2017 11:38 AM
I'm short in time to code it fully, but next steps may help you:
- read the variable (weeks of pregnancy) as chracter variable like $11;
- check length of the input variable
- try to find the most fitting format per length and treat the variable according to its length.
/* given dates */ if length = 8 then date=input(var,mmddyy8.); else if length = 10 then date = input(var,mmddyy10.); else /* given weeks */ if length le 2 then weeks = input(var,best2.); else if index(var,'weeks') > 0 then weeks = scan(var,1,'w/'); else ... etc...
in more compicated input you may need check existence of some characters ('w', '/', '.' etc)
and adapt treatment to the results.
After firs run, check are there informats not resolved or resolved not correctly, and expand your code until satisfaction.
a month ago