convert date to year, month, day for several variables with different name formats

[Monica Muller]

Hi All,

I have 10 different variables with the word "date" in their name. The names have two parts, word "date" and a unique part.But the order of these two parts is different. For example, dateinposition and hiredate. I want to split all of these dates into their year, month, and day and make the new variables' names to the unique part+year, unique part+month, and uniquepart+day

All these variables are string and in yyyy-mm-dd format.

My new variables would be:

inpositionyear inpositionmonth inpositionday hireyear hiremonth hireday

I know how to use split command for one variable, but I don't know how to use a loop for all variables when the names have different formats.

I really appreciate your help.

[Saint Fko]

Hi: You can try the following code: I assume that the date is always at the beginning or at the end.

local mainpart inposition hire ///list all the main parts here
foreach l of local mainpart {
cap rename `l'date `l'
cap rename date`l' `l'
gen `l'year=substr(`l',1,4)
gen `l'month=substr(`l',6,2)
gen `l'day=substr(`l',9,2)
}

[Carole J. Wilson]

If you have a lot of variables and don't want to specify the main parts of each variable, another way to go is below. It holds a list of all variables containing "date" and replaces that string with "", effectively erasing the "date" from the variable name, then combining it with year/month/day:

foreach var of varlist *date* {
    local main=subinstr("`var'","date","", .)
    rename `var' `main'
    gen `main'year=substr(`l',1,4)
    gen `main'month=substr(`l',6,2)
    gen `main'day=substr(`l',9,2)
    }

[Carole J. Wilson]

Or using split instead of substr (remove the -destring- part if you want to keep your new variables as string:

foreach var of varlist *date* {
    local main=subinstr("`var'","date","", .)
    rename `var' `main'
    split `main', p(-) destring
    rename `main'1 `main'year
    rename `main'2 `main'month
    rename `main'3 `main'day
    drop `main'
     }

[Monica Muller]

Thank you so much for the help. The code works perfectly.