Replacing variable labels if same prefix, different suffix

[Clara Galeazzi]

I have multiple sheets of excel that I import one by one into Stata. Each of the sheets has about 150 variables. The suffix of the variables is always either *_dum or *_yr. In Stata, I want to keep the label of *_dum, and the value of *_yr.

Regardless of the number of variables and the specific prefixes in each sheet, I want to create a code that will replace the variable label of those that end with "_yr" by the label in "_dum", if they have the same prefix. Prefixes will vary depending on the Excel input.

For instance 7_2_2_1_dum label needs to replace 7_2_2_1_yr label. Having both the label and the value that I want in 7_2_2_1_yr, I can now drop 7_2_2_1_dum.

Thank you.

[daniel klein]

It is not completely clear to me what you mean by "label". Do you mean the variable label, the value label, or are you actually referring to the variable name? Please show a minimal data example demonstrating what you have and what you want, preferably using dataex.

Best
Daniel

[William Lisowski]

I agree with Daniel that it seems unclear what you mean by "variable label" since I don't see how one would obtain a Stata variable label from an Excel import. But, nevertheless, suppose that you do indeed mean a Stata variable label. The following code would seem to do what you want.

foreach yrvar of varlist *_yr {
    local dumvar : subinstr local yrvar "_yr" "_dum"
    capture local lbl : variable label `dumvar'
    if _rc==0 {
        label variable `yrvar' `"`lbl'"'
        }
    }

. describe x* z*

              storage   display    value
variable name   type    format     label      variable label
-------------------------------------------------------------------------------------------------
x_yr            float   %9.0g                 label I don't want
x_dum           float   %9.0g                 label I do want
z_yr            float   %9.0g                 label I also don't want

. 
. foreach yrvar of varlist *_yr {
  2.     local dumvar : subinstr local yrvar "_yr" "_dum"
  3.         capture local lbl : variable label `dumvar'
  4.         if _rc==0 {
  5.             label variable `yrvar' `"`lbl'"'
  6.                 }
  7.         }

. describe x* z*

              storage   display    value
variable name   type    format     label      variable label
-------------------------------------------------------------------------------------------------
x_yr            float   %9.0g                 label I do want
x_dum           float   %9.0g                 label I do want
z_yr            float   %9.0g                 label I also don't want

[Nick Cox]

The nowhere documented command _crcslbl copies variable labels from one variable to another. There is no help file but this example shows how it works.

.

 clear

. set obs 1
number of observations (_N) was 0, now 1

. gen frog = 42

. label variable frog "something to do with frogs"

. gen toad = 666

. _crcslbl toad frog

. describe

Contains data
  obs:             1                          
 vars:             2                          
 size:             8                          
--------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
--------------------------------------------------------------------------------------
frog            float   %9.0g                 something to do with frogs
toad            float   %9.0g                 something to do with frogs
--------------------------------------------------------------------------------------
Sorted by: 
     Note: Dataset has changed since last saved.

That said, inside this command, which is just Stata code, it's just doing what Wlliam's code does -- in fact not as well, as his code loops and _crcslbl only applies to one variable at a time, so you would still need a loop.

Still, some people find arcane details intriguing. If you are one. then just store the information away and bring it out as dark knowledge when it might impress, but be vague about how you found out.

PS What's in a name? slbl presumably meant "same label" at some point, and the underscore is a way to uglify names and reduce the possibility of a clash with your own code. But what's with crc? Old-timers will recall that the company which is now StataCorp was once called Computing Resources Center. The name is a clue to the question of how they could develop Stata before they sold it; they were making money doing other things.

[Clara Galeazzi]

Yes, Daniel, I did mean variable label, I only put that clarification in once in my question, apologies. In terms of how my labels got there from Excel: each Excel column has one row that I used as the variable name, and one row I used as the variable label.

Thank you William, I really appreciate it.

Thank you for the command and the trivia Nick, I didn't know StataCorp was once Computing Resources Center.

[David Benson]

Clara,

if that's the case (i.e. first row is variable name, 2nd row is variable label), this post Label variables automatically gives an alternate solution.

Essentially, if 1st value of the variable is the variable label:

foreach var of varlist * {
    label variable `var' "`=`var'[1]'"
}

** This also works
foreach var of varlist * {
      local var_label = `var'[1]  // Have to do = so it takes the contents of `var'[1]
      label variable `var' "`var_label'"
}

[Clara Galeazzi]

Thank you David, this is what I'm currently doing to import variable names and labels into Stata. The problem is that, once these have been imported and I make some changes to them, I want to keep the variable label of variables ending in *_dum, and the values of variables ending in *_yr.. I think that William's answer is the one that does this most efficiently.

[Bikash Khura]

Dear All,

I have two variable area name and hospital name
area name contains 500 different value label.

e.g. 1 "RAMNAGAR"
2 "HARIDWAR"
3 "RAMGARH"
4 "RANCHI"
5 "WEST SINGHBHUM"
etc....
same way hospital name also has 1000 different value label
e.g. 1 "st mary hospital"
2 "sion hospital"
3 "josep hosiptal"
etc....

I want to create a variable new hospital name which contain the define label of area name as a prefix with the hospital name.

for example I want my new hospital name as:

for 1 observation the value label for the new hospital name= 1 "RAMNAGAR-st mary hospital"

Note: please consider that with in a area name there might be different name of hospital with different value label for hospital name.
and so on....

let me know if it is possible do. if yes, then can anyone please assist me on this?

thank you