cleaning date fields

cleaning date fields

by Laura Koth -

hello everyone,

I'm working on my final project and the date fields are coded in the following manner:

2013-10-22 00:00:00.0

all this is in one field

I have spent a good part of the day trying to figure out how to generate a new date field from this data but to no avail. Ultimately I need to convert the dob and date of form into a usable date so that I can subtract one from the other to arrive at a "age" at the visit variable.

thank you!

Laura


Re: cleaning date fields

by Atul Kumar -
This set of code will give you one way to recode your data.
Suppose your date field is coded by a sting variable "date1" and the entries look like this "2013-10-22 00:00:00.0"

gen date2 = substr(date1,1,10)   // to extract the first 10 characters from date1 into a string variable date2
destring date2, gen(date3) ignore("-")   // to remove the "-" and store it into a number format variable date3
tostring date3, gen(date4)  // to transform date3 back into string format and store it into date4
gen date4 = date(date3, "YMD")  // read date4 as a date
format date4 %td   //reformat date4

Re: cleaning date fields

by Laura Koth -

thanks for your post!

has anyone ever heard of using the following one line of command to do the same thing:

gen new_date_field = date(original_date_field, "YMD###")

it seems like it worked to generate a new date field that I could then format using "format new_date %td"