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:
10 1/7
04/12/13
04/12/2013
10
10.1
10weeks
10w1d
101/7 weeks
thanks.
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.
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.
For example:
/* 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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: