Issue with rename on a foreach loop

[Layla Peli]

Hi

I am trying to start a loop so that I can rename my variables. I tried using the same format I used for another foreach loop that works fine, but not sure why this one gives me a syntax error on the rename.

foreach v of varlist D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH { //the letters here refer to the columns of the excel I am using
local x : variable label `v' //the local macro x is storing the labels of variable v (or associated to column v)
rename `v' y`x' //the new name will be y*label*
format y`x' %td //formating to date DDmonYYYY
}

Would be really grateful for any hints to where I might be going wrong with this. Probably because I have been fussing on it for the past hour, I cannot seem to see the answer.

Thanks in advance
LP

[Nick Cox]

This should work if and only if each variable label is suitable as a suffix so that y`label' is indeed a legal and different variable name.

So, any character other than letters, numbers and underscores will cause an error. This should be closer to what you need.

foreach v of varlist D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH {
    local x = strtoname("y`: variable label `v''")  
    rename `v' `x'
}
format y* %td

(Thanks to Daniel for the fix. Oddly, my experiments not shown here used the correct function name.)

[daniel klein]

You do not show any output. Are you sure those variables, that is D, E, ..., AH, really have labels? I do not believe that import excel adds any labels to the column letters.

Edit:

Crossed with Nick's answer that points in the same direction. In his code, replace str2name with strtoname. Note that this does not help much if I am right and there are indeed no variable labels at all.

Best
Daniel

[Layla Peli]

Dear Nick and Daniel,

Thank you for your prompt replies.

Regarding Daniel's question for the labels, in previous lines I have imported the excel file where I have all the data.(panel data: for each company have annual closing prices) I need to make use of the years later on. But I used firstrow to take the dates which are all in the first row as variable label, starting from column D and on. I expected to get problems in the import stage by using the first row because the first columns are simply descriptions i.e. name of company. But the rest of the columns have numeric values since they are dates, and I know Stata does not allow for varibale names to be numeric, so that is why I wanted to add the y to render it a string.

The code proposed works, but since strtoname turns everything to string, I guess I will have to destring first and then use the %td. (I now get the error that varlist must contain all numeric or all string variables) I will look into this a little bit more : )

Thank you for the invaluable help, time and patience.

[Nick Cox]

strtoname() does nothing to your variables. All that code does is work out a suitable new variable name. It does not mean, in itself, that destring is needed. destring works on variable values, not their values.

You are correct that y20190314 (or whatever) would be fine as a variable name.

I was a bit puzzled that all these variables are formatted as daily dates. In fact that would be wrong, as they are values for different dates, and not dates themselves.

If this doesn't help enough, you'll have to do what we ask, read the FAQ Advice and give us a data example.

It sounds as if your data layout is not fit for purpose either, but one thing at a time.

[Layla Peli]

Hi again.

Just wanted to get back to everyone who helped me previously with my question; thank you again! Unfortunately, I still have to fix the issue. I now get this error message instead:
string %fmt required for string variables r(120).


I am including this time a sample of the data I am using.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str45 Name str15 Code str45 C str10(D E F G H)
"DEVOTEAM SA - FISCAL PERIOD END DATE"          "276439(WC05350)" "NA"         "NA"         "NA"         "NA"         "NA"         "NA"        
"ID LOGISTICS - FISCAL PERIOD END DATE"         "87032H(WC05350)" "NA"         "NA"         "NA"         "NA"         "NA"         "NA"        
"CAMAIEU - FISCAL PERIOD END DATE"              "289277(WC05350)" "NA"         "NA"         "NA"         "NA"         "NA"         "NA"        
"LAGARDERE ACTIVE - FISCAL PERIOD END DATE"     "929277(WC05350)" " 9/30/1987" " 9/30/1988" " 9/30/1989" " 9/30/1990" " 9/30/1991" " 9/30/1992"
"COMPAGNIE LA HENIN - FISCAL PERIOD END DATE"   "923870(WC05350)" "12/31/1987" "12/31/1988" "12/31/1989" "12/31/1990" "NA"         "NA"        
"STE IMMEUBLES FRANCE - FISCAL PERIOD END DATE" "936688(WC05350)" "NA"         "NA"         "NA"         "NA"         "NA"         "NA"        
"MASTRAD FINANCES - FISCAL PERIOD END DATE"     "35738F(WC05350)" "NA"         "NA"         "NA"         "NA"         "NA"         "NA"        
"IMALLIANCE - FISCAL PERIOD END DATE"           "885563(WC05350)" "NA"         "NA"         "NA"         "NA"         "NA"         "NA"        
"SERIB - TE ETU & REA - FISCAL PERIOD END DATE" "776562(WC05350)" "12/31/1987" "12/31/1988" "12/31/1989" "12/31/1990" "12/31/1991" "12/31/1992"
"VILMORIN & CIE - FISCAL PERIOD END DATE"       "309810(WC05350)" "NA"         "NA"         "NA"         "NA"         "NA"         "NA"        
end

Just to summarize once more what I am trying to do. Initially my year 1987 is recorded as string while the other years are all numeric. Since I cannot use numeric values for variable names, I have renamed each variable using it's label +y (in front) to make it a string. I have treated my first year 1987 separately since date format is MM/DD/YY., I extract the dates, store as integer using the date function (variable saved as y1987), and then format to %td. I then want to do the same with the loop:

foreach v of varlist D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH {
local x = strtoname("y`:variable label `v''")
rename `v' `x'
format `x' %td
}

I tried using
local x = subinstr(strtoname("y`: variable label `v''"),"_", "", .)
since it gave me error mentioned above, but the result is still the same.

Thank you once again for your time and patience!

Best,
L.P.

[Nick Cox]

You need to convert your string dates to numeric dates before you can assign a daily date format.

 
clear 
set obs 1 
gen test = "9/30/87" 
gen dailydate = daily(test, "MDY", 2020)
format dailydate %td

list test dailydate 

     +---------------------+
     |    test   dailydate |
     |---------------------|
  1. | 9/30/87   30sep1987 |
     +---------------------+