BookmarkSubscribeRSS Feed
newgrad
Calcite | Level 5

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.

3 REPLIES 3
Reeza
Super User

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.

Shmuel
Garnet | Level 18

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.

 

 

     

Peter_C
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 335 views
  • 0 likes
  • 4 in conversation