topic Re: multiple formats in variable - data cleaning in SAS Programming

multiple formats in variable - data cleaning

newgrad — Tue, 18 Jul 2017 14:22:32 GMT

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.1

10weeks

10w1d

101/7 weeks

thanks.

Re: multiple formats in variable - data cleaning

Reeza — Tue, 18 Jul 2017 15:10:01 GMT

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.

Re: multiple formats in variable - data cleaning

Shmuel — Tue, 18 Jul 2017 15:38:04 GMT

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.

Re: multiple formats in variable - data cleaning

Peter_C — Thu, 20 Jul 2017 08:46:50 GMT

Given that input:
10 1/7
04/12/13
04/12/2013
10
10.1
10weeks
10w1d
101/7 weeks

it would be nice if the original poster could indicate his interpretation of the value required