Encoding string

[Philipp Grab]

Good evening,

as a stata beginner I run into a problem preparing my data set.

I have to calculate the average of the spreads out of 4 columns which are sometimes the 3 digit numbers but also sometimes 3 digits and 1 decimal place. Therefore I would like to know how I can exclude the "L+" at the beginning but also the "/ x%" where applicable. Additionally sometimes there is no data point and therefore the average should not include them. Furthermore sometimes the column just shows a range "L+150-200" in which case I would need the average as well.

My goal is to have in each column just the spread value, f.ex. "325", and then I calculate the average.

Thanks in advance

These are the columns I am struggling with:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str19 RCPricing str18 TLAPricing str19 TLBPricing str13 TLCPricing str10 TLDPricing
"L+325"      "L+325"        "L+350 / 4%"     ""           ""          
"L+325"      ""             "L+325"          ""           ""          
"L+350"      ""             "L+350 / 3.25%"  ""           ""          
"L+200"      ""             "L+375 / 3%"     ""           ""          
"L+250"      "L+250"        "L+275 / 3%"     ""           ""          
"L+200"      ""             "L+425 / 3.25%"  ""           ""          
"L+350"      ""             "L+400 / 3.5%"   ""           ""          
""           ""             "L+250"          ""           ""          
"L+225"      ""             "L+500 / 3.25%"  ""           ""          
"L+175"      ""             "L+500 / 3.5%"   ""           ""          
"L+400"      ""             "L+400 / 3.25%"  ""           ""          
"L+275"      "L+250"        "L+275 / 3%"     ""           ""          
""           ""             "L+500 / 3.5%"   ""           ""          
""           ""             "L+800 / 3.5%"   ""           ""          
"L+500"      ""             "L+500 / 3%"     ""           ""          
"L+400"      ""             "L+400 / 3.75%"  ""           ""          
""           ""             "L+375 / 3.5%"   ""           ""          
"L+400"      "L+425"        "L+450 / 3%"     ""           ""          
"L+325"      "L+325"        "L+350 / 3%"     ""           ""          
"L+150"      ""             "L+250"          ""           ""          
"L+450"      ""             "L+450 / 3.5%"   ""           ""          
""           ""             "L+325"          ""           ""          
""           ""             ""               "L+225 / 3%" ""          
"L+200"      ""             "L+750 / 3.25%"  ""           ""          
"L+375"      "L+375"        "L+400 / 3%"     ""           ""          
"L+400"      "L+400"        "L+450 / 3%"     ""           ""          
""           ""             "L+350 / 3%"     ""           ""          
""           ""             "L+450 / 3.25%"  ""           ""          
""           ""             "L+250"          ""           ""          
"L+250"      "L+250"        "L+250"          ""           ""          
"L+625"      "L+625 / 3.5%" ""               ""           ""          
"L+275"      ""             "L+275 / 3%"     ""           ""          
""           ""             "L+525 / 3.25%"  ""           ""          
""           "L+250"        "L+325"          ""           ""          
""           ""             "L+300 / 3%"     ""           ""          
"L+525"      ""             "L+525 / 3.25%"  ""           ""          
"L+425"      ""             "L+425 / 3%"     ""           ""          
"L+225"      "L+275"        "L+325"          ""           ""          
"L+500"      ""             "L+500 / 3%"     ""           ""          
"L+375"      ""             "L+450 / 3%"     ""           ""          
"L+450"      ""             "L+475 / 3%"     ""           ""          
"L+325"      "L+325"        "L+350 / 3%"     ""           ""          
"L+250"      "L+250"        "L+300 / 3%"     ""           ""          
"L+500"      ""             "L+500 / 3%"     ""           ""          
""           ""             "L+350 / 3.25%"  ""           ""          
"L+450"      ""             "L+450 / 3.25%"  ""           ""          
"L+400"      ""             "L+400 / 3%"     ""           ""          
"L+400"      ""             "L+400 / 3.25%"  ""           ""          
"L+400"      "L+400"        "L+600 / 3.25%"  ""           ""          
"L+650"      "L+650"        "L+650 / 3.5%"   ""           ""          
""           ""             "L+600 / 3.5%"   ""           ""          
"L+600"      ""             "L+600 / 3%"     ""           ""          
"L+400"      "L+400"        "L+450 / 2%"     ""           ""          
"L+350"      ""             "L+350 / 3%"     ""           ""          
""           ""             ""               "L+325 / 2%" ""          
"L+350"      ""             "L+440 / 3.25%"  ""           ""          
"L+400"      ""             "L+400 / 2.5%"   ""           ""          
""           ""             "L+650 / 3%"     ""           ""          
"L+450"      ""             "L+600 / 3%"     ""           ""          
""           ""             "L+450 / 3%"     ""           ""          
""           ""             "L+750 / 3%"     ""           ""          
"L+450"      ""             "L+500 / 2%"     ""           ""          
"L+350"      ""             "L+375 / 2%"     ""           ""          
""           ""             "L+350"          ""           ""          
""           ""             ""               ""           "L+350 / 2%"
""           ""             ""               ""           ""          
"NA"         ""             "L+675 / 2%"     ""           ""          
""           ""             "L+600 / 2.5%"   ""           ""          
""           ""             ""               ""           ""          
"L+600"      ""             "L+700 / 2%"     ""           ""          
""           ""             "L+500 / 2%"     ""           ""          
"L+350"      ""             "L+350 / 2%"     ""           ""          
"L+550"      ""             "L+550 / 3%"     ""           ""          
""           ""             ""               ""           ""          
"L+400 / 2%" ""             "L+400 / 2%"     ""           ""          
"L+425"      ""             "L+425 / 2.25%"  ""           ""          
"L+825"      ""             "L+825 / 3%"     ""           ""          
""           ""             "L+987.5 / 2.5%" ""           ""          
"L+325"      ""             "L+325 / 2.5%"   ""           ""          
"L+450"      ""             "L+475 / 2.25%"  ""           ""          
""           ""             "L+550 / 2%"     ""           ""          
""           ""             "L+1000 / 3.5%"  ""           ""          
"L+500"      ""             "L+500 / 2%"     ""           ""          
""           ""             "L+750 / 2%"     ""           ""          
"L+500"      "L+425 / 2%"   ""               ""           ""          
""           ""             "L+475 / 2%"     ""           ""          
""           ""             "L+375 / 2%"     ""           ""          
"L+425 / 2%" ""             "L+425 / 2%"     ""           ""          
"L+400"      ""             "L+400 / 2%"     ""           ""          
"L+350"      "L+350"        "L+375 / 2%"     ""           ""          
"L+325"      "L+325"        "L+325 / 2%"     ""           ""          
"L+450"      ""             "L+450 / 2%"     ""           ""          
"NA"         ""             "L+350 / 2%"     ""           ""          
"NA"         ""             "L+550 / 2%"     ""           ""          
"L+375"      ""             "L+400 / 2%"     ""           ""          
""           ""             "L+450 / 2%"     ""           ""          
"L+350"      ""             "L+350 / 2.25%"  ""           ""          
""           ""             ""               ""           ""          
"L+350"      ""             "L+350 / 2%"     ""           ""          
"L+500"      ""             "L+600 / 2.25%"  ""           ""          
end

[William Lisowski]

Welcome to Stataist.

Below I give sample code that converts your five variables from string variables to numeric variables and computes the average of the non-missing values in each observation.

Your data did not have any example of "a range "L+150-200" " and I don't know what you would want that range converted to for use in computing the average of the 5 variables. The minimum 150? The maximum 200? The midpoint 175?

local prices RCPricing TLAPricing TLBPricing TLCPricing TLDPricing
foreach var of varlist `prices' {
    replace `var' = subinstr(`var', "NA","",1)
    replace `var' = subinstr(`var',"L+","",1)
    replace `var' = substr(`var',1,strpos(`var'," / ")-1) if strpos(`var'," / ")>0
    assert `var'=="" | real(`var')!=.
    }
destring `prices', replace
egen avg = rowmean(`prices')
list, clean

. list, clean

       RCPric~g   TLAPri~g   TLBPri~g   TLCPri~g   TLDPri~g        avg  
  1.        325        325        350          .          .   333.3333  
  2.        325          .        325          .          .        325  
  3.        350          .        350          .          .        350  
  4.        200          .        375          .          .      287.5  
  5.        250        250        275          .          .   258.3333  
  6.        200          .        425          .          .      312.5  
  7.        350          .        400          .          .        375  
  8.          .          .        250          .          .        250  
  9.        225          .        500          .          .      362.5  
 10.        175          .        500          .          .      337.5  
 11.        400          .        400          .          .        400  
 12.        275        250        275          .          .   266.6667  
 13.          .          .        500          .          .        500  
 14.          .          .        800          .          .        800  
 15.        500          .        500          .          .        500  
 16.        400          .        400          .          .        400  
 17.          .          .        375          .          .        375  
 18.        400        425        450          .          .        425  
 19.        325        325        350          .          .   333.3333  
 20.        150          .        250          .          .        200  
 21.        450          .        450          .          .        450  
 22.          .          .        325          .          .        325  
 23.          .          .          .        225          .        225  
 24.        200          .        750          .          .        475  
 25.        375        375        400          .          .   383.3333  
 26.        400        400        450          .          .   416.6667  
 27.          .          .        350          .          .        350  
 28.          .          .        450          .          .        450  
 29.          .          .        250          .          .        250  
 30.        250        250        250          .          .        250  
 31.        625        625          .          .          .        625  
 32.        275          .        275          .          .        275  
 33.          .          .        525          .          .        525  
 34.          .        250        325          .          .      287.5  
 35.          .          .        300          .          .        300  
 36.        525          .        525          .          .        525  
 37.        425          .        425          .          .        425  
 38.        225        275        325          .          .        275  
 39.        500          .        500          .          .        500  
 40.        375          .        450          .          .      412.5  
 41.        450          .        475          .          .      462.5  
 42.        325        325        350          .          .   333.3333  
 43.        250        250        300          .          .   266.6667  
 44.        500          .        500          .          .        500  
 45.          .          .        350          .          .        350  
 46.        450          .        450          .          .        450  
 47.        400          .        400          .          .        400  
 48.        400          .        400          .          .        400  
 49.        400        400        600          .          .   466.6667  
 50.        650        650        650          .          .        650  
 51.          .          .        600          .          .        600  
 52.        600          .        600          .          .        600  
 53.        400        400        450          .          .   416.6667  
 54.        350          .        350          .          .        350  
 55.          .          .          .        325          .        325  
 56.        350          .        440          .          .        395  
 57.        400          .        400          .          .        400  
 58.          .          .        650          .          .        650  
 59.        450          .        600          .          .        525  
 60.          .          .        450          .          .        450  
 61.          .          .        750          .          .        750  
 62.        450          .        500          .          .        475  
 63.        350          .        375          .          .      362.5  
 64.          .          .        350          .          .        350  
 65.          .          .          .          .        350        350  
 66.          .          .          .          .          .          .  
 67.          .          .        675          .          .        675  
 68.          .          .        600          .          .        600  
 69.          .          .          .          .          .          .  
 70.        600          .        700          .          .        650  
 71.          .          .        500          .          .        500  
 72.        350          .        350          .          .        350  
 73.        550          .        550          .          .        550  
 74.          .          .          .          .          .          .  
 75.        400          .        400          .          .        400  
 76.        425          .        425          .          .        425  
 77.        825          .        825          .          .        825  
 78.          .          .      987.5          .          .      987.5  
 79.        325          .        325          .          .        325  
 80.        450          .        475          .          .      462.5  
 81.          .          .        550          .          .        550  
 82.          .          .       1000          .          .       1000  
 83.        500          .        500          .          .        500  
 84.          .          .        750          .          .        750  
 85.        500        425          .          .          .      462.5  
 86.          .          .        475          .          .        475  
 87.          .          .        375          .          .        375  
 88.        425          .        425          .          .        425  
 89.        400          .        400          .          .        400  
 90.        350        350        375          .          .   358.3333  
 91.        325        325        325          .          .        325  
 92.        450          .        450          .          .        450  
 93.          .          .        350          .          .        350  
 94.          .          .        550          .          .        550  
 95.        375          .        400          .          .      387.5  
 96.          .          .        450          .          .        450  
 97.        350          .        350          .          .        350  
 98.          .          .          .          .          .          .  
 99.        350          .        350          .          .        350  
100.        500          .        600          .          .        550

[Philipp Grab]

Thank you very much William. I also understood your code and this helps me for the future.
Now I tried to work on the lines with the range which obviously after your code remain with "150 - 250". I want STATA to use the Midpoint of the range.
Apparently, I cannot calculcate the mean directly. How do you approach this problem in STATA?

Thanks in advance.

My code:

local prices RCPricing TLAPricing TLBPricing TLCPricing TLDPricing
foreach var of varlist `prices' {
    replace `var' = subinstr(`var', "NA","",1)
    replace `var' = subinstr(`var',"L+","",1)
    replace `var' = substr(`var',1,strpos(`var'," / ")-1) if strpos(`var'," / ")>0
    replace `var' = mean(substr(`var',1,3),substr(`var',7,3)) if strpos(`var'," - ")>0
    assert `var'=="" | real(`var')!=.
    }
destring `prices', replace
egen Spread_avg = rowmean(`prices')/100

I expanded the list to 400 obs. to show you the lines with a range.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str19 RCPricing str18 TLAPricing str19 TLBPricing str13 TLCPricing str10 TLDPricing
"L+400"               "L+400"         "L+600 / 3.25%"  ""             ""          
"L+650"               "L+650"         "L+650 / 3.5%"   ""             ""          
""                    ""              "L+600 / 3.5%"   ""             ""          
"L+600"               ""              "L+600 / 3%"     ""             ""          
"L+400"               "L+400"         "L+450 / 2%"     ""             ""          
"L+350"               ""              "L+350 / 3%"     ""             ""          
""                    ""              ""               "L+325 / 2%"   ""          
"L+350"               ""              "L+440 / 3.25%"  ""             ""          
"L+400"               ""              "L+400 / 2.5%"   ""             ""          
""                    ""              "L+650 / 3%"     ""             ""          
"L+450"               ""              "L+600 / 3%"     ""             ""          
""                    ""              "L+450 / 3%"     ""             ""          
""                    ""              "L+750 / 3%"     ""             ""          
"L+450"               ""              "L+500 / 2%"     ""             ""          
"L+350"               ""              "L+375 / 2%"     ""             ""          
""                    ""              "L+350"          ""             ""          
""                    ""              ""               ""             "L+350 / 2%"
""                    ""              ""               ""             ""          
"NA"                  ""              "L+675 / 2%"     ""             ""          
""                    ""              "L+600 / 2.5%"   ""             ""          
""                    ""              ""               ""             ""          
"L+600"               ""              "L+700 / 2%"     ""             ""          
""                    ""              "L+500 / 2%"     ""             ""          
"L+350"               ""              "L+350 / 2%"     ""             ""          
"L+550"               ""              "L+550 / 3%"     ""             ""          
""                    ""              ""               ""             ""          
"L+400 / 2%"          ""              "L+400 / 2%"     ""             ""          
"L+425"               ""              "L+425 / 2.25%"  ""             ""          
"L+825"               ""              "L+825 / 3%"     ""             ""          
""                    ""              "L+987.5 / 2.5%" ""             ""          
"L+325"               ""              "L+325 / 2.5%"   ""             ""          
"L+450"               ""              "L+475 / 2.25%"  ""             ""          
""                    ""              "L+550 / 2%"     ""             ""          
""                    ""              "L+1000 / 3.5%"  ""             ""          
"L+500"               ""              "L+500 / 2%"     ""             ""          
""                    ""              "L+750 / 2%"     ""             ""          
"L+500"               "L+425 / 2%"    ""               ""             ""          
""                    ""              "L+475 / 2%"     ""             ""          
""                    ""              "L+375 / 2%"     ""             ""          
"L+425 / 2%"          ""              "L+425 / 2%"     ""             ""          
"L+400"               ""              "L+400 / 2%"     ""             ""          
"L+350"               "L+350"         "L+375 / 2%"     ""             ""          
"L+325"               "L+325"         "L+325 / 2%"     ""             ""          
"L+450"               ""              "L+450 / 2%"     ""             ""          
"NA"                  ""              "L+350 / 2%"     ""             ""          
"NA"                  ""              "L+550 / 2%"     ""             ""          
"L+375"               ""              "L+400 / 2%"     ""             ""          
""                    ""              "L+450 / 2%"     ""             ""          
"L+350"               ""              "L+350 / 2.25%"  ""             ""          
""                    ""              ""               ""             ""          
"L+350"               ""              "L+350 / 2%"     ""             ""          
"L+500"               ""              "L+600 / 2.25%"  ""             ""          
"L+800 / 2.5%"        ""              "L+950 / 2.5%"   ""             ""          
"L+450"               ""              "L+325 / 2%"     ""             ""          
""                    "L+475"         "L+475"          ""             ""          
"L+575"               ""              "L+575 / 2.25%"  ""             ""          
""                    ""              "L+1000 / 2%"    ""             ""          
""                    ""              ""               "L+400 / 2%"   ""          
"L+400"               ""              "L+400 / 2%"     ""             ""          
"L+350"               ""              "L+350 / 2%"     ""             ""          
"L+250"               ""              "L+500 / 2.5%"   ""             ""          
""                    ""              "L+275"          ""             ""          
""                    ""              "L+350 / 2.25%"  ""             ""          
"L+375"               ""              "L+400 / 2%"     ""             ""          
"L+400"               ""              "L+400 / 2.25%"  ""             ""          
"L+425 / 2%"          ""              "L+375 / 2%"     ""             ""          
"L+475"               ""              "L+475 / 2%"     ""             ""          
"L+325"               ""              ""               "L+300 / 1.5%" ""          
""                    "L+350 / 2%"    ""               ""             ""          
""                    ""              ""               ""             ""          
"L+550"               ""              "L+550 / 2%"     ""             ""          
"L+400"               ""              "L+400 / 2%"     ""             ""          
""                    ""              "L+650"          ""             ""          
"L+350"               ""              "L+350 / 1.5%"   ""             ""          
"L+950"               ""              "L+950 / 2.5%"   ""             ""          
"L+400"               ""              "L+400 / 2%"     ""             ""          
"L+350"               ""              "L+350 / 2%"     ""             ""          
"L+400"               ""              "L+350 / 2%"     ""             ""          
"L+725"               ""              "L+725 / 2%"     ""             ""          
"L+400 / 2%"          ""              "L+400 / 2%"     ""             ""          
"L+350"               ""              "L+350 / 1.75%"  ""             ""          
"L+400 / 2%"          ""              "L+400 / 2%"     ""             ""          
"L+450"               ""              "L+500 / 2%"     ""             ""          
"L+325"               ""              "L+325 / 1.75%"  ""             ""          
""                    ""              "L+500 / 2%"     ""             ""          
"L+350"               ""              "L+400 / 1.5%"   ""             ""          
"L+350"               ""              "L+400 / 2%"     ""             ""          
""                    ""              "L+325 / 1.75%"  ""             ""          
"L+400 / 2%"          ""              "L+400 / 2%"     ""             ""          
"L+350"               ""              "L+750 / 3%"     ""             ""          
"L+375"               ""              "L+375 / 2%"     ""             ""          
""                    ""              "L+500 / 2%"     ""             ""          
"L+550"               ""              "L+600 / 2%"     ""             ""          
""                    ""              "L+400 / 2%"     ""             ""          
"L+500"               ""              "L+550 / 2%"     ""             ""          
"L+475"               ""              "L+500 / 1.5%"   ""             ""          
""                    "L+425 / 2%"    "L+525 / 2%"     ""             ""          
""                    ""              "L+475 / 2%"     ""             ""          
"L+300"               ""              "L+400 / 2%"     ""             ""          
""                    ""              "L+325 / 1.5%"   ""             ""          
"L+400 / 2%"          ""              "L+400 / 2%"     ""             ""          
"L+475 / 2%"          ""              "L+475 / 2%"     ""             ""          
"L+350"               ""              "L+325 / 1.5%"   ""             ""          
"L+450 / 2%"          ""              "L+450 / 2%"     ""             ""          
"L+300"               ""              "L+300"          ""             ""          
"L+400"               ""              "L+400 / 1.75%"  ""             ""          
"L+475 / 1.5%"        ""              "L+475 / 1.5%"   ""             ""          
""                    ""              "L+350 / 1.5%"   ""             ""          
"L+425 / 1.75%"       ""              "L+425 / 1.75%"  ""             ""          
"L+450"               ""              "L+450 / 1.75%"  ""             ""          
""                    ""              "L+425 / 1.75%"  ""             ""          
"L+400 / 2%"          ""              "L+400 / 2%"     ""             ""          
"L+400 / 2%"          ""              "L+400 / 2%"     ""             ""          
"L+400 / 2%"          ""              "L+400 / 1.5%"   ""             ""          
"L+400"               ""              "L+375 / 1.75%"  ""             ""          
"L+375 / 2%"          ""              "L+375 / 2%"     ""             ""          
"L+375 / 2%"          ""              ""               ""             ""          
"L+300 / 1.75%"       "L+300 / 1.75%" "L+300 / 1.75%"  ""             ""          
"L+525 / 2%"          ""              "L+550 / 2%"     ""             ""          
"L+275 / 1.5%"        ""              "L+375 / 1.5%"   ""             ""          
"L+600 / 2%"          ""              "L+600 / 2%"     ""             ""          
"L+400"               ""              "L+500 / 2%"     ""             ""          
""                    ""              "L+800 / 2%"     ""             ""          
"L+300"               ""              "L+350 / 1.5%"   ""             ""          
"L+475 / 2%"          ""              "L+500 / 2%"     ""             ""          
""                    ""              "L+550 / 2%"     ""             ""          
"L+350"               ""              "L+350 / 1.5%"   ""             ""          
"L+550 / 2%"          ""              "L+550 / 2%"     ""             ""          
"L+350 / 1.5%"        ""              "L+300 / 1.5%"   ""             ""          
"L+375"               ""              "L+375 / 1.75%"  ""             ""          
"L+425"               ""              "L+475 / 2%"     ""             ""          
"L+600"               ""              "L+600 / 2%"     ""             ""          
""                    ""              "L+550 / 1.75%"  ""             ""          
"L+450"               ""              "L+450 / 2%"     ""             ""          
"L+375 / 2%"          ""              "L+350 / 1.5%"   ""             ""          
"L+425 / 1.5%"        ""              "L+425 / 1.5%"   ""             ""          
"L+350 / 1.5%"        ""              "L+350 / 1.5%"   ""             ""          
"L+350 / 1.75%"       ""              "L+350 / 1.75%"  ""             ""          
"L+300"               "L+300"         "L+300 / 1.25%"  ""             ""          
"L+425"               ""              "L+725 / 2%"     ""             ""          
"L+500 / 2%"          ""              "L+500 / 2%"     ""             ""          
"L+450 / 2%"          ""              "L+500 / 1.75%"  ""             ""          
"L+300 / 1.5%"        ""              "L+325 / 1.5%"   ""             ""          
"L+450"               ""              "L+450 / 1.75%"  ""             ""          
""                    ""              ""               ""             ""          
"L+375"               ""              "L+750 / 2%"     ""             ""          
"L+400"               ""              "L+400 / 1.5%"   ""             ""          
"L+400 / 1.5%"        ""              "L+400 / 1.5%"   ""             ""          
""                    ""              "L+300 / 1.5%"   ""             ""          
"L+300"               "L+300"         "L+300 / 1.5%"   ""             ""          
""                    ""              "L+425 / 1.5%"   ""             ""          
""                    ""              "L+400 / 1.75%"  ""             ""          
"L+450"               ""              "L+450 / 1.75%"  ""             ""          
"NA"                  ""              "L+500 / 2%"     ""             ""          
""                    ""              "L+375 / 1.75%"  ""             ""          
"L+300"               ""              "L+350 / 1.5%"   ""             ""          
"L+400 / 1.5%"        ""              "L+400 / 1.5%"   ""             ""          
"L+400"               ""              "L+400 / 1.5%"   ""             ""          
"L+450 / 2%"          ""              "L+450 / 1.75%"  ""             ""          
"L+350 / 1.5%"        ""              "L+550 / 1.5%"   ""             ""          
"L+450 / 1.75%"       ""              "L+450 / 1.75%"  ""             ""          
"L+325 / 1.5%"        ""              "L+325 / 1.5%"   ""             ""          
""                    ""              "L+350 / 1.75%"  ""             ""          
""                    ""              "L+800 / 3%"     ""             ""          
"L+625"               ""              "L+625 / 2%"     ""             ""          
"L+375 / 1.75%"       ""              "L+375 / 1.75%"  ""             ""          
"L+525 / 2%"          ""              "L+525 / 2%"     ""             ""          
"L+425 / 2%"          ""              "L+425 / 2%"     ""             ""          
"L+400 / 2%"          ""              "L+450 / 2%"     ""             ""          
"L+450"               ""              ""               ""             ""          
"L+350"               ""              "L+350 / 1.5%"   ""             ""          
"NA"                  ""              "L+300 / 1.5%"   ""             ""          
"L+350"               ""              "L+350"          ""             ""          
"L+425"               ""              "L+425 / 1.75%"  ""             ""          
"L+475 / 1.75%"       ""              "L+475 / 1.75%"  ""             ""          
"L+450 / 2%"          ""              "L+550 / 2%"     ""             ""          
"L+425"               ""              "L+425 / 1.75%"  ""             ""          
""                    ""              "L+375 / 1.5%"   ""             ""          
"NA"                  ""              "L+600 / 2%"     ""             ""          
"L+425"               "L+425"         "L+450 / 1.5%"   ""             ""          
""                    ""              "L+575 / 2%"     ""             ""          
"L+475"               ""              "L+500 / 1.5%"   ""             ""          
"L+450"               ""              "L+450 / 1.5%"   ""             ""          
""                    ""              "L+475"          ""             ""          
"L+325"               "L+325"         "L+400 / 1.5%"   ""             ""          
"L+375"               ""              "L+650 / 1.5%"   ""             ""          
""                    "NA"            "L+700 / 2%"     ""             ""          
"L+525 / 1.75%"       ""              "L+525 / 1.75%"  ""             ""          
"L+450"               ""              "L+575 / 2%"     ""             ""          
"L+475 / 1.75%"       ""              "L+575 / 1.75%"  ""             ""          
"L+500"               ""              "L+500 / 1.75%"  ""             ""          
"L+450"               ""              "L+450 / 1.75%"  ""             ""          
"L+400 / 1.5%"        "L+400 / 1.5%"  "L+500 / 1.75%"  ""             ""          
"L+600 / 1.75%"       ""              "L+600 / 1.75%"  ""             ""          
""                    ""              "L+925 / 2%"     ""             ""          
"L+600 / 1.75%"       ""              "L+700 / 2%"     ""             ""          
""                    ""              "L+950 / 2.5%"   ""             ""          
"L+350"               ""              "L+750 / 2%"     ""             ""          
"L+450"               ""              "L+600 / 2%"     ""             ""          
"L+425"               ""              "L+750 / 2%"     ""             ""          
"L+375 - 400"         "L+400"         "L+400 / 1.5%"   ""             ""          
""                    ""              "L+625 / 2%"     ""             ""          
"L+250"               ""              "L+325 / 1.5%"   ""             ""          
"L+475"               ""              "L+500 / 1.75%"  ""             ""          
""                    ""              "L+700 / 2%"     ""             ""          
"L+550"               ""              "L+550 / 1.75%"  ""             ""          
""                    ""              "L+375 / 1.5%"   ""             ""          
"L+350"               ""              "L+425 / 1.75%"  ""             ""          
"L+500 / 1.75%"       ""              "L+500 / 1.75%"  ""             ""          
"L+475 / 1.75%"       ""              "L+475 / 1.75%"  ""             ""          
"L+450 / 1.75%"       "L+450 / 1.75%" "L+450 / 1.75%"  ""             ""          
"L+400"               "L+400"         "L+450 / 1.75%"  ""             ""          
"NA"                  "NA"            "L+850 / 2%"     ""             ""          
""                    ""              "L+600 / 1.75%"  ""             ""          
"L+350"               "L+350"         "L+400 / 1.5%"   ""             ""          
"L+450"               ""              "L+500 / 1.75%"  ""             ""          
"L+450"               ""              "L+450 / 1.75%"  ""             ""          
"L+425 / 1.75%"       "L+425 / 1.75%" "L+450 / 1.75%"  ""             ""          
"L+400"               ""              "L+400 / 1.5%"   ""             ""          
""                    ""              "L+775 / 2.25%"  ""             ""          
"L+225"               ""              "L+600 / 1.75%"  ""             ""          
""                    ""              "L+900 / 2%"     ""             ""          
""                    ""              "L+375 / 2%"     ""             ""          
"L+325"               ""              "L+900 / 2%"     ""             ""          
""                    ""              "L+450 / 1.75%"  ""             ""          
""                    ""              "L+425 / 1.75%"  ""             ""          
"L+300 - 350"         ""              "L+825 / 2%"     ""             ""          
"NA"                  ""              "L+500 / 1.75%"  ""             ""          
"L+525 / 1.75%"       ""              "L+550 / 1.75%"  ""             ""          
"L+475"               ""              "L+525 / 1.75%"  ""             ""          
"L+575 / 1.75%"       ""              "L+575 / 1.75%"  ""             ""          
""                    ""              "L+400 / 1.5%"   ""             ""          
""                    ""              "L+1000 / 2%"    ""             ""          
""                    ""              ""               ""             ""          
"L+275"               ""              "L+400 / 1.5%"   ""             ""          
""                    ""              ""               ""             ""          
""                    ""              "L+575 / 2%"     ""             ""          
""                    ""              "L+525 / 1.75%"  ""             ""          
"L+525 / 1.75%"       ""              "L+525 / 1.75%"  ""             ""          
""                    ""              "L+425 / 1.75%"  ""             ""          
"L+450"               ""              "L+450 / 1.75%"  ""             ""          
"L+475"               ""              "L+475 / 1.75%"  ""             ""          
"L+425 - 450 / 1.75%" "L+425 / 1.75%" "L+450 / 1.75%"  ""             ""          
"NA"                  ""              "L+600 / 1.75%"  ""             ""          
"L+550"               ""              "L+550 / 1.75%"  ""             ""          
""                    ""              "L+450 / 1.25%"  ""             ""          
"L+400"               ""              "L+400 / 1.75%"  ""             ""          
"L+350"               "L+350"         "L+400 / 1.5%"   ""             ""          
"L+450"               ""              "L+450 / 1.75%"  ""             ""          
"L+475"               ""              "L+450 / 1.75%"  ""             ""          
"L+400"               "L+400"         "L+400 / 1.5%"   ""             ""          
""                    "L+450 / 1.75%" "L+475 / 1.75%"  ""             ""          
""                    ""              "L+300 / 1.5%"   ""             ""          
"L+450 / 1.75%"       ""              "L+450 / 1.75%"  ""             ""          
"NA"                  ""              "L+500 / 1.75%"  ""             ""          
""                    ""              "L+300 / 1.5%"   ""             ""          
"L+550"               ""              ""               ""             ""          
"L+750 - 775 / 1.75%" ""              "L+1000 / 2%"    ""             ""          
""                    "L+375"         "L+450 / 1.5%"   ""             ""          
"L+450"               ""              "L+450 / 1.5%"   ""             ""          
"L+475 / 1.75%"       ""              "L+575 / 1.75%"  ""             ""          
"L+325"               ""              "L+700 / 2%"     ""             ""          
""                    ""              "L+600 / 1.75%"  ""             ""          
"L+400"               ""              "L+400 / 2%"     ""             ""          
""                    ""              "L+425 / 1.5%"   ""             ""          
"L+275"               "L+275"         "L+300 / 1.5%"   ""             ""          
"L+400 / 1.5%"        ""              "L+400 / 1.5%"   ""             ""          
"L+350"               ""              "L+350 / 1.5%"   ""             ""          
"L+475"               ""              "L+500 / 1.75%"  ""             ""          
"L+350"               ""              "L+400 / 1.5%"   ""             ""          
"L+400"               ""              "L+400 / 1.75%"  ""             ""          
"L+550 / 1.75%"       ""              "L+550 / 1.75%"  ""             ""          
"L+400 / 1.5%"        ""              "L+400 / 1.5%"   ""             ""          
"NA"                  ""              "L+500 / 1.75%"  ""             ""          
"NA"                  ""              "L+425 / 1.75%"  ""             ""          
""                    ""              "L+400 / 1.75%"  ""             ""          
"L+300"               "L+300"         "L+350 / 1.5%"   ""             ""          
"L+300 - 350"         ""              "L+900 / 2%"     ""             ""          
""                    ""              ""               ""             ""          
"NA"                  ""              "L+375 / 1.5%"   ""             ""          
"L+575 / 1.5%"        ""              "L+575 / 1.5%"   ""             ""          
"L+225"               "L+225"         "L+325"          ""             ""          
"L+425 - 450"         ""              "L+425 / 1.75%"  ""             ""          
"L+550 / 1.75%"       ""              "L+550 / 1.75%"  ""             ""          
""                    ""              "L+325"          ""             ""          
"L+425 / 1.5%"        ""              "L+425 / 1.5%"   ""             ""          
""                    ""              "L+425 / 1.75%"  ""             ""          
"L+375"               ""              "L+375 / 1.5%"   ""             ""          
"L+425"               ""              ""               ""             ""          
"L+500 / 1.75%"       ""              "L+500 / 1.75%"  ""             ""          
""                    ""              "L+375"          ""             ""          
"L+450"               ""              "L+400 / 1.75%"  ""             ""          
"NA"                  ""              "L+350 / 1.5%"   ""             ""          
"L+250"               ""              "L+400 / 1.5%"   ""             ""          
""                    ""              ""               ""             ""          
"L+400"               ""              "L+375 / 1.5%"   ""             ""          
"L+375 / 1.5%"        ""              "L+350 / 1.5%"   ""             ""          
""                    ""              "L+750 / 1.5%"   ""             ""          
"L+550 / 1.5%"        ""              "L+550 / 1.5%"   ""             ""          
"L+525 / 1.75%"       ""              "L+525 / 1.75%"  ""             ""          
"L+275"               ""              ""               ""             ""          
"L+400 / 1.5%"        ""              "L+350 / 1.5%"   ""             ""          
"L+425 / 1.5%"        ""              "L+425 / 1.5%"   ""             ""          
"L+400"               ""              "L+575 / 1.75%"  ""             ""          
""                    ""              "L+475 / 1.5%"   ""             ""          
"L+450"               ""              "L+450 / 1.5%"   ""             ""          
"L+300"               "L+300"         "L+325 / 1.5%"   ""             ""          
""                    ""              "L+600 / 1.75%"  ""             ""          
""                    ""              "L+400 / 1.5%"   ""             ""          
""                    ""              ""               ""             ""          
"L+500 / 1.75%"       ""              "L+525 / 1.75%"  ""             ""          
"L+325"               ""              "L+350 / 1.5%"   ""             ""          
"L+400 / 1.5%"        ""              "L+400 / 1.5%"   ""             ""          
""                    ""              "L+800 / 1.75%"  ""             ""          
"L+425 / 1.5%"        ""              "L+425 / 1.5%"   ""             ""          
"L+400 / 1.5%"        ""              "L+400 / 1.5%"   ""             ""          
"L+300"               ""              "L+300 / 1.5%"   ""             ""          
"L+375"               ""              "L+500 / 1.5%"   ""             ""          
"L+450 / 1.75%"       ""              "L+450 / 1.75%"  ""             ""          
"NA"                  ""              "L+425 / 1.5%"   ""             ""          
""                    ""              "L+550 / 1.75%"  ""             ""          
""                    ""              "L+500 / 1.75%"  ""             ""          
"L+475"               ""              "L+475 / 1.5%"   ""             ""          
"L+400 - 425 / 1.5%"  ""              "L+375 / 1.5%"   ""             ""          
""                    ""              "L+600 / 1.75%"  ""             ""          
"L+500 / 1.75%"       ""              "L+500 / 1.75%"  ""             ""          
"L+325"               ""              "L+325 / 1%"     ""             ""          
"L+425"               ""              "L+850 / 2%"     ""             ""          
"L+450"               ""              "L+450 / 1.5%"   ""             ""          
"L+375"               ""              "L+425 / 1.5%"   ""             ""          
"L+450 / 1.75%"       ""              "L+450 / 1.75%"  ""             ""          
"L+500"               ""              "L+500 / 1.75%"  ""             ""          
""                    ""              "L+850 / 2%"     ""             ""          
"L+225"               ""              ""               ""             ""          
"L+500 / 1.5%"        ""              ""               ""             ""          
"L+600 / 1.75%"       ""              "L+600 / 1.75%"  ""             ""          
"L+475 / 1.5%"        ""              "L+475 / 1.5%"   ""             ""          
"L+225"               ""              "L+450 / 1.75%"  ""             ""          
"L+375 / 1.5%"        ""              "L+375 / 1.5%"   ""             ""          
""                    ""              "L+600 / 1.5%"   ""             ""          
"NA"                  ""              "L+375 / 1.5%"   ""             ""          
"NA"                  ""              "L+450 / 1.75%"  ""             ""          
"L+375"               ""              "L+375 / 1.5%"   ""             ""          
"L+475 - 500 / 1.75%" ""              "L+550 / 1.75%"  ""             ""          
""                    ""              "L+500 / 1.75%"  ""             ""          
"L+475 / 1.75%"       ""              "L+450 / 1.75%"  ""             ""          
"L+475 - 500 / 1.5%"  ""              "L+550 / 1.5%"   ""             ""          
"L+575 / 1.75%"       ""              "L+575 / 1.75%"  ""             ""          
"L+550 / 1.75%"       ""              "L+550 / 1.75%"  ""             ""          
"L+225"               ""              "L+350 / 1.5%"   ""             ""          
"NA"                  ""              "L+500 / 1.5%"   ""             ""          
""                    ""              "L+550 / 1.5%"   ""             ""          
"NA"                  ""              "L+700 / 1.75%"  ""             ""          
"L+525 / 1.5%"        ""              "L+525 / 1.5%"   ""             ""          
"L+500 / 1.5%"        ""              "L+500 / 1.5%"   ""             ""          
"L+450 / 1.75%"       ""              "L+400 / 1.5%"   ""             ""          
""                    ""              "L+425 / 1.5%"   ""             ""          
"L+500 / 1.5%"        ""              "L+500 / 1.5%"   ""             ""          
"L+425"               ""              "L+425 / 1.5%"   ""             ""          
"L+650 / 1.75%"       ""              "L+650 / 1.75%"  ""             ""          
""                    "L+250"         "L+300 / 1%"     ""             ""          
"L+500 / 1.75%"       ""              "L+500 / 1.75%"  ""             ""          
"L+650 / 1.75%"       ""              "L+650 / 1.75%"  ""             ""          
"L+275"               ""              "L+550 / 1.5%"   ""             ""          
"L+350"               "L+350"         "L+350 / 1.5%"   ""             ""          
"L+450 / 1%"          ""              "L+400 / 1%"     ""             ""          
""                    ""              "L+337.5"        ""             ""          
""                    "L+250"         "L+300 / 1%"     ""             ""          
"L+300"               "L+300"         "L+300 / 1%"     ""             ""          
""                    ""              "L+400 / 1%"     ""             ""          
"L+350"               ""              "L+375 / 1.5%"   ""             ""          
""                    ""              "L+350"          ""             ""          
""                    ""              "L+450 / 1.5%"   ""             ""          
"L+525 / 1.75%"       ""              "L+525 / 1.75%"  ""             ""          
"L+400 / 1.5%"        ""              "L+375 / 1.25%"  ""             ""          
"NA"                  ""              "L+350 / 1.5%"   ""             ""          
"L+450"               ""              "L+400 / 1.5%"   ""             ""          
"L+275 / 1%"          ""              "L+275 / 1%"     ""             ""          
"NA"                  ""              "L+325 / 1.5%"   ""             ""          
""                    ""              "L+325 / 1%"     ""             ""          
"L+250"               ""              ""               ""             ""          
""                    ""              ""               "L+325 / 1.5%" ""          
""                    ""              "L+325 / 1%"     ""             ""          
"L+450"               ""              ""               ""             ""          
"L+250"               ""              "L+450 / 1.5%"   ""             ""          
""                    ""              "L+325 / 1.5%"   ""             ""          
""                    ""              "L+350 / 1.5%"   ""             ""          
"L+250"               "L+250"         "L+275 / 0.75%"  ""             ""          
""                    ""              "L+600 / 1.5%"   ""             ""          
"NA"                  ""              "L+600 / 1.5%"   ""             ""          
"L+325 / 1.5%"        ""              "L+300 / 1.25%"  ""             ""          
""                    ""              "L+300 / 1.5%"   ""             ""          
""                    ""              ""               ""             ""          
"L+550"               ""              "L+550 / 1.5%"   ""             ""          
"L+350 / 1.5%"        ""              "L+350 / 1.5%"   ""             ""          
""                    ""              "L+300 / 1%"     ""             ""          
""                    ""              "L+300 / 1.25%"  ""             ""          
""                    ""              "L+350 / 1.5%"   ""             ""          
"NA"                  ""              "L+325 / 1.25%"  ""             ""          
"L+350"               ""              ""               ""             ""          
end

[William Lisowski]

There are three problems with

replace `var' = mean(substr(`var',1,3),substr(`var',7,3)) if strpos(`var'," - ")>0

First, the expressions

substr(`var',1,3) and substr(`var',7,3)

are strings and you cannot do arithmetic on strings. You need

real(substr(`var',1,3)) and real(substr(`var',7,3))

to do arithmetic with.

Second, there is no mean() function. (There is a egen mean function as part of the egen command, but it does not do what you want.) So you need to add the two numbers and divide by 2.

( real(substr(`var',1,3)) + real(substr(`var',7,3)) ) / 2

Third, because your variable is a string variable at this point, you need to convert the result from a number back to a string to replace the value of the variable with.

replace `var' = strofreal( ..., "9.0g")

Putting that all together

replace `var' = strofreal( ( real(substr(`var',1,3)) + real(substr(`var',7,3)) ) / 2, "9.0g" ) if strpos(`var'," - ")>0