[Giles German]

Clyde,

Out of courtesy I just wanted to update you on this....

I have managed to resolve the issue using a letter instead of a number so I can use the same macro as you set originally called "sites". The code is running as I type.

I still have not managed to get the ordering issue sorted but will open as a new thread if I need support to try and reduce the confusion.

Giles

[Giles German]

i am just working through the ordering of the macros and have run into difficulties the following error

file /Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector10/Regioneu/1.953649759292603.dta not found
variable site not found
r(111);

Stata is picking up the p_model1 value as the variable name (so close!!)

Data set plus worked example below.....


* Example generated by -dataex-. To install: ssc install dataex
clear
input str87 Stock str9 StockCode float region str2 site str10 exch long Sector1 float p_Model1
"Yu Group PLC YU.:LSE" "YU." 3 "eu" "YU..L" 10 1.9536498
"Empresa Distribuidora y Comercializadora Norte SA EDN:NYQ" "EDN" 3 "u" "" 10 .6723835
"Beijing Enterprises Water Group Ltd HUN2:FRA" "HUN2" 3 "eu" "HUN2.F" 10 .1943116
"American States Water Co AWR:NYQ" "AWR" 3 "u" "" 10 .19115694
"American States Water Co FDK:FRA" "FDK" 3 "eu" "FDK.F" 10 .190062
"Mercury NZ Ltd MGHTF:PNK" "MGHTF" 3 "u" "" 10 .187866
"Pembina Pipeline Corp P5P:FRA" "P5P" 3 "eu" "P5P.F" 10 .1841431
"Pembina Pipeline Corp. PBA:NYQ" "PBA" 3 "u" "" 10 .18383025
"ONEOK Inc OKE:NYQ" "OKE" 3 "u" "" 10 .18007235
"Aqua America Inc A2A:BER" "A2A" 3 "eu" "A2A.BE" 10 .1745505
"Aqua America Inc WTR:NYQ" "WTR" 3 "u" "" 10 .1743941
"Ultrapar Participacoes SA UGP:NYQ" "UGP" 3 "u" "" 10 .17302632
"Middlesex Water Co MSEX:NSQ" "MSEX" 3 "u" "" 10 .16821465
"New Jersey Resources Corp NJR:NYQ" "NJR" 3 "u" "" 10 .1586567
"California Water Service Group CWT:NYQ" "CWT" 3 "u" "" 10 .15459895
"TTW PCL F1P:FRA" "F1P" 3 "eu" "F1P.F" 10 .1545492
"California Water Service Group WT5:BER" "WT5" 3 "eu" "WT5.BE" 10 .153504
"Ormat Technologies Inc ORA:NYQ" "ORA" 3 "u" "" 10 .14909956
"Ormat Technologies Inc ORA:NYQ" "ORA" 3 "u" "" 10 .14909956
"Keppel Infrastructure Trust KPLIF:PNK" "KPLIF" 3 "u" "" 10 .13975017
"Enbw Energie Baden Wuerttemberg AG EBK:FRA" "EBK" 3 "eu" "EBK.F" 10 .13503219
"Dian Swastatika Sentosa Tbk PT DSX:BER" "DSX" 3 "eu" "DSX.BE" 10 .13276142
"Athena Investments A/S GRTSF:PNK" "GRTSF" 3 "u" "" 10 .12976353
"South Jersey Industries Inc SJI:NYQ" "SJI" 3 "u" "" 10 .12947209
"South Jersey Industries Inc J4S:FRA" "J4S" 3 "eu" "J4S.F" 10 .12790787
"Rubis SCA RUI:PAR" "RUI" 3 "eu" "RUI.PA" 10 .12787674
"Enel Americas SA ENIA:NYQ" "ENIA" 3 "u" "" 10 .12765475
"Athens Water and Sewerage Company SA EYP:BER" "EYP" 3 "eu" "EYP.BE" 10 .12760505
"Snam SpA SRG:MIL" "SRG" 3 "eu" "SRG.MI" 10 .12455654
"Enel Americas SA NER,R:MUN" "NER,R" 3 "eu" "NER,R.MU" 10 .1235878
"Chesapeake Utilities Corp CPK:NYQ" "CPK" 3 "u" "" 10 .12345844
"Chesapeake Utilities Corp CPK:NYQ" "CPK" 3 "u" "" 10 .12345844
"SJW Group SJW:NYQ" "SJW" 3 "u" "" 10 .12241271
"Chesapeake Utilities Corp CH5:FRA" "CH5" 3 "eu" "CH5.F" 10 .1223635
"Chesapeake Utilities Corp CH5:FRA" "CH5" 3 "eu" "CH5.F" 10 .1223635
"PGE Polska Grupa Energetyczna SA PPOEF:PNK" "PPOEF" 3 "u" "" 10 .12053046
"EDP Renovaveis SA EDPR:LIS" "EDPR" 3 "eu" "EDPR.LS" 10 .11502533
"Severn Trent PLC SVT:LSE" "SVT" 3 "eu" "SVT.L" 10 .11397702
"Iren SpA IRE:MIL" "IRE" 3 "eu" "IRE.MI" 10 .11255188
"Iren SpA TZ8:BER" "TZ8" 3 "eu" "TZ8.BE" 10 .11255188
"Tokai Holdings Corp 3167:TYO" "3167" 3 "j" "" 10 .11030423
"American Water Works Company Inc AWK:NYQ" "AWK" 3 "u" "" 10 .1074386
"Star Group LP SGU:NYQ" "SGU" 3 "u" "" 10 .10721345
"A2A SpA A2A:MIL" "A2A" 3 "eu" "A2A.MI" 10 .1070382
"2G energy AG 2GBX:GER" "2GBX" 3 "eu" "2GBX.DE" 10 .10557172
"Tallinna Vesi AS A1T:BER" "A1T" 3 "eu" "A1T.BE" 10 .10513643
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"WGL Holdings Inc WGL:NYQ" "WGL" 3 "u" "" 10 .09952857
"Pne Wind AG PNEX.N:GER" "PNEX.N" 3 "eu" "PNEX.N.DE" 10 .09937995
"UGI Corp UGI:NYQ" "UGI" 3 "u" "" 10 .09915674
"Acea SpA ACE:MIL" "ACE" 3 "eu" "ACE.MI" 10 .09829516
"NiSource Inc NI:NYQ" "NI" 3 "u" "" 10 .09591556
"Enel Generacion Chile SA EOCC:NYQ" "EOCC" 3 "u" "" 10 .09384745
"Enel SpA ENL:GER" "ENL" 3 "eu" "ENL.DE" 10 .0913777
"Enel SpA ENL:GER" "ENL" 3 "eu" "ENL.DE" 10 .0913777
"Alliant Energy Corp LNT:NYQ" "LNT" 3 "u" "" 10 .0912176
"National Fuel Gas Co NFG:NYQ" "NFG" 3 "u" "" 10 .090762
"Enel SpA ENEL:MIL" "ENEL" 3 "eu" "ENEL.MI" 10 .09075201
"Enel SpA ENEL:MIL" "ENEL" 3 "eu" "ENEL.MI" 10 .09075201
"Tokyo Electric Power Company Holdings Inc 9501:TYO" "9501" 3 "j" "" 10 .08911946
"CMS Energy Corp CMS:NYQ" "CMS" 3 "u" "" 10 .08893535
"Vectren Corp VVC:NYQ" "VVC" 3 "u" "" 10 .08645996
"HK Electric Investments Ltd HKCVF:PNK" "HKCVF" 3 "u" "" 10 .08574605
"Energiekontor AG EKTX:GER" "EKTX" 3 "eu" "EKTX.DE" 10 .08558413
"Energiekontor AG EKTX:GER" "EKTX" 3 "eu" "EKTX.DE" 10 .08558413
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"YTL Corporation Bhd 1773.T:TYO" "1773.T" 3 "j" "" 10 .08398792
"China Datang Corp Renewable Power Co Ltd DT7:FRA" "DT7" 3 "eu" "DT7.F" 10 .08346357
end

local p 10

label values Sector1 Sector1
label def Sector1 10 "Utilities", modify

local f 1
keep if Sector1 == `p'
levelsof site, local(sites)
foreach i of local sites {
levelsof StockCode if site == "`i'", local(stock`i')

gsort -p_Model1
count
local nobs `r(N)'
local stocks
forvalues i = 1/`nobs' {
local stocks `stocks' `=p_Model1[`i']'


}


foreach i of local sites {
*foreach s of local stock`i' {

foreach s of local stocks {
summ site if StockCode == `"`s'"', meanonly
local site `r(mean)'
capture noisily {
local f = `f'+1

use "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/STATA Data 5.dta", replace

merge 1:1 Date using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector`p'/Region`i'/`s'.dta", nogenerate

}
}
}
}

[Clyde Schechter]

Where you write

forvalues i = 1/`nobs' {
local stocks `stocks' `=p_Model1[`i']'


}

You are putting the values of p_Model1 into local stocks. Then when you get to

    foreach s of local stocks {
        summ site if StockCode == `"`s'"', meanonly
        local site `r(mean)'
        capture noisily {
            local f = `f'+1

            use "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/STATA Data 5.dta", replace

            merge 1:1 Date using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector`p'/Region`i'/`s'.dta", nogenerate

            }
        }
    }

those values of p_Model1 come out as values of `s', so your -merge- command tries to find files whose names are the values of p_Model1--and those don't exist. What you want to put into local stocks are the values of the stock codes, not the values of p_Model1.

So that first block of code should be:

forvalues i = 1/`nobs' {
local stocks `stocks' `=StockCode[`i']'


}

Added: Looking back earlier in this thread, I see that this error was in my original code when you asked about ordering the local macro of stocks in order of p_Model1. Sorry for the error and the time you lost because of it.

[Giles German]

It is now looking for site as a variable it has got the correct file path.... Error as follows: -

file /Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector10/Regioneu/YU.dta not found
variable site not found
r(111);

When you wrote the code site was a number I have changed it to letters maybe this is the problem?

[Clyde Schechter]

I don't know how your files are named. Read the error message: Stata is looking for a file called /Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector10/Regioneu/YU.dta and it isn't there.

So the question is what it should be looking for. Only you know this. The code fixes most of the pathname. All that is variable is the /Region*/ part, where * might be replaced by eu, u, or j, and then the filename itself. Are Regioneu, Regionu and Regionj the correct names for directories in your path system? The filenames themselves are, in the current code, taken from the StockCode variable. Do you have a file named YU..dta--apparently not. Note also that if you are running on a Mac the matching criteria for pathnames and filenames are even more stringent: for Mac they are case sensitive, so a file named yu..dta will not match with YU..dta there.

So what is the filename that Stata should be looking for, and where will it be found in your data set? You have to populate the local macro stocks from there.

So, first, go look at the names of the files you are working with. Do the file names look like the values in Stock Code, or are they something else? If something else, you have to locate where they are to be found in your data set and change the code accordingly. If they look basically like the values of Stock Code then check the file names for errors. (For example, you may have a file named YU.dta--which is not going to match YU..dta. If the stock code contains a punctuation mark like a . then the filename will have to have it also in order to match it, and the . that separates the extension dta from the main file name does not count--both have to be there.) You may have to change some of the file names if they do not actually match the stock codes. Or you may have to change the stock codes in your data in order for them to match the filenames.

[Giles German]

Clyde,

You are of course right it couldn't find the file. I have made sure that the file is there and the code worked until it got to the next file that isn't there.

The idea of the code is that it uses the capture noisily option to continuously run the code so I can pick up errors later. I want it to run with this function so it i fully automated and I can leave the code running over night.

With the previous code it worked with this one when the file is not there it looks for a variable site which is in a different data set as we have just uploaded and merged the new data.

Can you help me to get the capture noisily option to work with the code? It might be a case of reloading the other data set as part of the loop or altering the location of the capture noisily.

Thanks in advance

Giles

[Clyde Schechter]

So the code we are starting from, formatted for better readability, is:

foreach i of local sites {
    foreach s of local stocks {
        summ site if StockCode == `"`s'"', meanonly
        local site `r(mean)'
        capture noisily {
            local f = `f'+1

            use "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/STATA Data 5.dta", replace

            merge 1:1 Date using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector`p'/Region`i'/`s'.dta", nogenerate

            }
        }
    }
}

Now, you already have the -merge- command inside a capture block, so that is not what is causing the program to halt. The other error message you are getting, "variable site not found" is probably arising from -summ site if StockCode == `"`s'"', meanonly-, and that is probably happening because, although it is fine on the first iteration of the loop, when you come back for the second iteration, you have clobbered the data that was previously in there: you now have the contents of "STATA Data5.dta" after merging with one of the stock files. I suspect that the variable site is not present there. So what you actually need to do is -preserve- the data before you overwrite it, and then bring it back. So something like this:

foreach i of local sites {
    foreach s of local stocks {
        summ site if StockCode == `"`s'"', meanonly
        local site `r(mean)'
        preserve
        capture noisily {
            local f = `f'+1

            use "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/STATA Data 5.dta", replace

            merge 1:1 Date using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector`p'/Region`i'/`s'.dta", nogenerate
            if c(rc) {
                continue
            }
            // CODE THAT USES THE MERGED RESULTS GOES HERE
        }
        restore
    }
}

Now, I have to comment that this entire loop actually does nothing (except possibly generate error messages). You begin by finding the numeric value of site corresponding to the stock code. But then you never make use of that anywhere. Within the -capture noisily- block you read in one file and merge it with another. But then you don't do anything with that result. You don't calculate anything from it. You don't save it anywhere. Nothing: you create it and then abandon it. So perhaps you are showing just an excerpt of the code and the real thing does more. Whatever that may be, I suggest you put all of that inside the capture noisily block in the place where I have suggested so that if it, too, generates errors, you will continue to progress.

Note the small block beginning with -if c(rc) {-. If your merge does not work (say because the file with that name doesn't exist), then this will keep you from attempting to do whatever it is you need to do on the merged results and just move on to the next iteration of the loop. There is no point just generating error message after error message arising from not having the right data in memory when we can determine that we have the wrong file name near the top of the loop, as here.

[Giles German]

Clyde

You are correct that there is some code that comes after this. Unfortunately for me that code also has a preserve and restore which has to be there to point to the merged file so it keeps telling me there is nothing to restore as there is not. I have tried various ways of restoring and preserving at different points and I still can't get this one over the line. Any ideas!!!

  
foreach i of local sites {    
foreach s of local stocks {        
summ site if StockCode == `"`s'"', meanonly        
local site `r(mean)'        
preserve        
capture noisily {            
local f = `f'+1              
use "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/STATA Data 5.dta", replace    
        
merge 1:1 Date using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector`p'/Region`i'/`s'.dta", nogenerate    
      
if c(rc) {
              
continue             }  

preserve
drop Date `z'
local x
foreach var of varlist *{

local x `x' `var'  
}
restore              // CODE THAT USES THE MERGED RESULTS GOES HERE        
}        
restore    
}
}

[Clyde Schechter]

tempfile copy
foreach i of local sites {
     foreach s of local stocks {
         summ site if StockCode == `"`s'"', meanonly
         local site `r(mean)'
         save `"`copy'"', replace
         capture noisily {
            local f = `f'+1
            use "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/STATA Data 5.dta", replace
            merge 1:1 Date using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector`p'/Region`i'/`s'.dta", nogenerate
            if c(rc) {
                continue
            }
            preserve
            drop Date `z' 
            local x 
            foreach var of varlist *{
                local x `x' `var'
            }
            restore  // CODE THAT USES THE MERGED RESULTS GOES HERE
        }
        use `copy', clear
    } 
}

should do it.

[Giles German]

Clyde,

That has still not worked same error. Data set, code and error see below.....

* Example generated by -dataex-. To install: ssc install dataex
clear
input str87 Stock str9 StockCode float region str2 site str10 exch long Sector1 float p_Model1
"Yu Group PLC YU.:LSE" "YU" 3 "eu" "YU.L" 10 1.9536498
"Empresa Distribuidora y Comercializadora Norte SA EDN:NYQ" "EDN" 3 "u" "" 10 .6723835
"Beijing Enterprises Water Group Ltd HUN2:FRA" "HUN2" 3 "eu" "HUN2.F" 10 .1943116
"American States Water Co AWR:NYQ" "AWR" 3 "u" "" 10 .19115694
"American States Water Co FDK:FRA" "FDK" 3 "eu" "FDK.F" 10 .190062
"Mercury NZ Ltd MGHTF:PNK" "MGHTF" 3 "u" "" 10 .187866
"Pembina Pipeline Corp P5P:FRA" "P5P" 3 "eu" "P5P.F" 10 .1841431
"Pembina Pipeline Corp. PBA:NYQ" "PBA" 3 "u" "" 10 .18383025
"ONEOK Inc OKE:NYQ" "OKE" 3 "u" "" 10 .18007235
"Aqua America Inc A2A:BER" "A2A" 3 "eu" "A2A.BE" 10 .1745505
"Aqua America Inc WTR:NYQ" "WTR" 3 "u" "" 10 .1743941
"Ultrapar Participacoes SA UGP:NYQ" "UGP" 3 "u" "" 10 .17302632
"Middlesex Water Co MSEX:NSQ" "MSEX" 3 "u" "" 10 .16821465
"New Jersey Resources Corp NJR:NYQ" "NJR" 3 "u" "" 10 .1586567
"California Water Service Group CWT:NYQ" "CWT" 3 "u" "" 10 .15459895
"TTW PCL F1P:FRA" "F1P" 3 "eu" "F1P.F" 10 .1545492
"California Water Service Group WT5:BER" "WT5" 3 "eu" "WT5.BE" 10 .153504
"Ormat Technologies Inc ORA:NYQ" "ORA" 3 "u" "" 10 .14909956
"Ormat Technologies Inc ORA:NYQ" "ORA" 3 "u" "" 10 .14909956
"Keppel Infrastructure Trust KPLIF:PNK" "KPLIF" 3 "u" "" 10 .13975017
"Enbw Energie Baden Wuerttemberg AG EBK:FRA" "EBK" 3 "eu" "EBK.F" 10 .13503219
"Dian Swastatika Sentosa Tbk PT DSX:BER" "DSX" 3 "eu" "DSX.BE" 10 .13276142
"Athena Investments A/S GRTSF:PNK" "GRTSF" 3 "u" "" 10 .12976353
"South Jersey Industries Inc SJI:NYQ" "SJI" 3 "u" "" 10 .12947209
"South Jersey Industries Inc J4S:FRA" "J4S" 3 "eu" "J4S.F" 10 .12790787
"Rubis SCA RUI:PAR" "RUI" 3 "eu" "RUI.PA" 10 .12787674
"Enel Americas SA ENIA:NYQ" "ENIA" 3 "u" "" 10 .12765475
"Athens Water and Sewerage Company SA EYP:BER" "EYP" 3 "eu" "EYP.BE" 10 .12760505
"Snam SpA SRG:MIL" "SRG" 3 "eu" "SRG.MI" 10 .12455654
"Enel Americas SA NER,R:MUN" "NER,R" 3 "eu" "NER,R.MU" 10 .1235878
"Chesapeake Utilities Corp CPK:NYQ" "CPK" 3 "u" "" 10 .12345844
"Chesapeake Utilities Corp CPK:NYQ" "CPK" 3 "u" "" 10 .12345844
"SJW Group SJW:NYQ" "SJW" 3 "u" "" 10 .12241271
"Chesapeake Utilities Corp CH5:FRA" "CH5" 3 "eu" "CH5.F" 10 .1223635
"Chesapeake Utilities Corp CH5:FRA" "CH5" 3 "eu" "CH5.F" 10 .1223635
"PGE Polska Grupa Energetyczna SA PPOEF:PNK" "PPOEF" 3 "u" "" 10 .12053046
"EDP Renovaveis SA EDPR:LIS" "EDPR" 3 "eu" "EDPR.LS" 10 .11502533
"Severn Trent PLC SVT:LSE" "SVT" 3 "eu" "SVT.L" 10 .11397702
"Iren SpA IRE:MIL" "IRE" 3 "eu" "IRE.MI" 10 .11255188
"Iren SpA TZ8:BER" "TZ8" 3 "eu" "TZ8.BE" 10 .11255188
"Tokai Holdings Corp 3167:TYO" "3167" 3 "j" "" 10 .11030423
"American Water Works Company Inc AWK:NYQ" "AWK" 3 "u" "" 10 .1074386
"Star Group LP SGU:NYQ" "SGU" 3 "u" "" 10 .10721345
"A2A SpA A2A:MIL" "A2A" 3 "eu" "A2A.MI" 10 .1070382
"2G energy AG 2GBX:GER" "2GBX" 3 "eu" "2GBX.DE" 10 .10557172
"Tallinna Vesi AS A1T:BER" "A1T" 3 "eu" "A1T.BE" 10 .10513643
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"WGL Holdings Inc WGL:NYQ" "WGL" 3 "u" "" 10 .09952857
"Pne Wind AG PNEX.N:GER" "PNEX.N" 3 "eu" "PNEX.N.DE" 10 .09937995
"UGI Corp UGI:NYQ" "UGI" 3 "u" "" 10 .09915674
"Acea SpA ACE:MIL" "ACE" 3 "eu" "ACE.MI" 10 .09829516
"NiSource Inc NI:NYQ" "NI" 3 "u" "" 10 .09591556
"Enel Generacion Chile SA EOCC:NYQ" "EOCC" 3 "u" "" 10 .09384745
"Enel SpA ENL:GER" "ENL" 3 "eu" "ENL.DE" 10 .0913777
"Enel SpA ENL:GER" "ENL" 3 "eu" "ENL.DE" 10 .0913777
"Alliant Energy Corp LNT:NYQ" "LNT" 3 "u" "" 10 .0912176
"National Fuel Gas Co NFG:NYQ" "NFG" 3 "u" "" 10 .090762
"Enel SpA ENEL:MIL" "ENEL" 3 "eu" "ENEL.MI" 10 .09075201
"Enel SpA ENEL:MIL" "ENEL" 3 "eu" "ENEL.MI" 10 .09075201
"Tokyo Electric Power Company Holdings Inc 9501:TYO" "9501" 3 "j" "" 10 .08911946
"CMS Energy Corp CMS:NYQ" "CMS" 3 "u" "" 10 .08893535
"Vectren Corp VVC:NYQ" "VVC" 3 "u" "" 10 .08645996
"HK Electric Investments Ltd HKCVF:PNK" "HKCVF" 3 "u" "" 10 .08574605
"Energiekontor AG EKTX:GER" "EKTX" 3 "eu" "EKTX.DE" 10 .08558413
"Energiekontor AG EKTX:GER" "EKTX" 3 "eu" "EKTX.DE" 10 .08558413
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"YTL Corporation Bhd 1773.T:TYO" "1773.T" 3 "j" "" 10 .08398792
"China Datang Corp Renewable Power Co Ltd DT7:FRA" "DT7" 3 "eu" "DT7.F" 10 .08346357
end

local p 10


local f 1
keep if Sector1 == `p'
levelsof site, local(sites)

foreach i of local sites {
levelsof StockCode if site == "`i'", local(stock`i')
}
gsort -p_Model1
count
local nobs `r(N)'
local stocks
forvalues i = 1/`nobs' {
local stocks `stocks' `=StockCode[`i']'


}

tempfile copy
foreach i of local sites {
*foreach s of local stock`i' {

foreach s of local stocks {
summ site if StockCode == `"`s'"', meanonly
local site `r(mean)'
save `"`copy'"', replace
capture noisily {
local f = `f'+1

use "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/STATA Data 5.dta", replace

merge 1:1 Date using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector`p'/Region`i'/`s'.dta", nogenerate
if c(rc) {
                continue
            }
            preserve
            drop Date `z'
            local x
            foreach var of varlist *{
                local x `x' `var'
            }
            restore
}
use `copy', clear
}
}
}

variable site not found
r(111);

[William Lisowski]

I removed this post because the code I was commenting on was changed after the original comment was posted, and the comment is no longer relevant.

[Giles German]

Apologies William same error please re read I missed a bracket on my earlier post.

[Clyde Schechter]

I can only conclude that the error is arising in some code that you have not shown. When I run your code in #25, it goes without a hitch and produces no error messages.

[Giles German]

I have re written the whole file. As you can see it is not putting the correct on the file in this case it is putting a "u" when it should be putting an "eu". I have put the site variable,e to bed by moving the capture noisily before the "summ". I think we have nearly put this one to bed. Can you try the code below see if you can fix the problem described.

Giles

* Example generated by -dataex-. To install: ssc install dataex
clear
input str87 Stock str9 StockCode float region str2 site str10 exch long Sector1 float p_Model1
"Yu Group PLC YU.:LSE" "YU" 3 "eu" "YU.L" 10 1.9536498
"Empresa Distribuidora y Comercializadora Norte SA EDN:NYQ" "EDN" 3 "u" "" 10 .6723835
"Beijing Enterprises Water Group Ltd HUN2:FRA" "HUN2" 3 "eu" "HUN2.F" 10 .1943116
"American States Water Co AWR:NYQ" "AWR" 3 "u" "" 10 .19115694
"American States Water Co FDK:FRA" "FDK" 3 "eu" "FDK.F" 10 .190062
"Mercury NZ Ltd MGHTF:PNK" "MGHTF" 3 "u" "" 10 .187866
"Pembina Pipeline Corp P5P:FRA" "P5P" 3 "eu" "P5P.F" 10 .1841431
"Pembina Pipeline Corp. PBA:NYQ" "PBA" 3 "u" "" 10 .18383025
"ONEOK Inc OKE:NYQ" "OKE" 3 "u" "" 10 .18007235
"Aqua America Inc A2A:BER" "A2A" 3 "eu" "A2A.BE" 10 .1745505
"Aqua America Inc WTR:NYQ" "WTR" 3 "u" "" 10 .1743941
"Ultrapar Participacoes SA UGP:NYQ" "UGP" 3 "u" "" 10 .17302632
"Middlesex Water Co MSEX:NSQ" "MSEX" 3 "u" "" 10 .16821465
"New Jersey Resources Corp NJR:NYQ" "NJR" 3 "u" "" 10 .1586567
"California Water Service Group CWT:NYQ" "CWT" 3 "u" "" 10 .15459895
"TTW PCL F1P:FRA" "F1P" 3 "eu" "F1P.F" 10 .1545492
"California Water Service Group WT5:BER" "WT5" 3 "eu" "WT5.BE" 10 .153504
"Ormat Technologies Inc ORA:NYQ" "ORA" 3 "u" "" 10 .14909956
"Ormat Technologies Inc ORA:NYQ" "ORA" 3 "u" "" 10 .14909956
"Keppel Infrastructure Trust KPLIF:PNK" "KPLIF" 3 "u" "" 10 .13975017
"Enbw Energie Baden Wuerttemberg AG EBK:FRA" "EBK" 3 "eu" "EBK.F" 10 .13503219
"Dian Swastatika Sentosa Tbk PT DSX:BER" "DSX" 3 "eu" "DSX.BE" 10 .13276142
"Athena Investments A/S GRTSF:PNK" "GRTSF" 3 "u" "" 10 .12976353
"South Jersey Industries Inc SJI:NYQ" "SJI" 3 "u" "" 10 .12947209
"South Jersey Industries Inc J4S:FRA" "J4S" 3 "eu" "J4S.F" 10 .12790787
"Rubis SCA RUI:PAR" "RUI" 3 "eu" "RUI.PA" 10 .12787674
"Enel Americas SA ENIA:NYQ" "ENIA" 3 "u" "" 10 .12765475
"Athens Water and Sewerage Company SA EYP:BER" "EYP" 3 "eu" "EYP.BE" 10 .12760505
"Snam SpA SRG:MIL" "SRG" 3 "eu" "SRG.MI" 10 .12455654
"Enel Americas SA NER,R:MUN" "NER,R" 3 "eu" "NER,R.MU" 10 .1235878
"Chesapeake Utilities Corp CPK:NYQ" "CPK" 3 "u" "" 10 .12345844
"Chesapeake Utilities Corp CPK:NYQ" "CPK" 3 "u" "" 10 .12345844
"SJW Group SJW:NYQ" "SJW" 3 "u" "" 10 .12241271
"Chesapeake Utilities Corp CH5:FRA" "CH5" 3 "eu" "CH5.F" 10 .1223635
"Chesapeake Utilities Corp CH5:FRA" "CH5" 3 "eu" "CH5.F" 10 .1223635
"PGE Polska Grupa Energetyczna SA PPOEF:PNK" "PPOEF" 3 "u" "" 10 .12053046
"EDP Renovaveis SA EDPR:LIS" "EDPR" 3 "eu" "EDPR.LS" 10 .11502533
"Severn Trent PLC SVT:LSE" "SVT" 3 "eu" "SVT.L" 10 .11397702
"Iren SpA IRE:MIL" "IRE" 3 "eu" "IRE.MI" 10 .11255188
"Iren SpA TZ8:BER" "TZ8" 3 "eu" "TZ8.BE" 10 .11255188
"Tokai Holdings Corp 3167:TYO" "3167" 3 "j" "" 10 .11030423
"American Water Works Company Inc AWK:NYQ" "AWK" 3 "u" "" 10 .1074386
"Star Group LP SGU:NYQ" "SGU" 3 "u" "" 10 .10721345
"A2A SpA A2A:MIL" "A2A" 3 "eu" "A2A.MI" 10 .1070382
"2G energy AG 2GBX:GER" "2GBX" 3 "eu" "2GBX.DE" 10 .10557172
"Tallinna Vesi AS A1T:BER" "A1T" 3 "eu" "A1T.BE" 10 .10513643
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"WGL Holdings Inc WGL:NYQ" "WGL" 3 "u" "" 10 .09952857
"Pne Wind AG PNEX.N:GER" "PNEX.N" 3 "eu" "PNEX.N.DE" 10 .09937995
"UGI Corp UGI:NYQ" "UGI" 3 "u" "" 10 .09915674
"Acea SpA ACE:MIL" "ACE" 3 "eu" "ACE.MI" 10 .09829516
"NiSource Inc NI:NYQ" "NI" 3 "u" "" 10 .09591556
"Enel Generacion Chile SA EOCC:NYQ" "EOCC" 3 "u" "" 10 .09384745
"Enel SpA ENL:GER" "ENL" 3 "eu" "ENL.DE" 10 .0913777
"Enel SpA ENL:GER" "ENL" 3 "eu" "ENL.DE" 10 .0913777
"Alliant Energy Corp LNT:NYQ" "LNT" 3 "u" "" 10 .0912176
"National Fuel Gas Co NFG:NYQ" "NFG" 3 "u" "" 10 .090762
"Enel SpA ENEL:MIL" "ENEL" 3 "eu" "ENEL.MI" 10 .09075201
"Enel SpA ENEL:MIL" "ENEL" 3 "eu" "ENEL.MI" 10 .09075201
"Tokyo Electric Power Company Holdings Inc 9501:TYO" "9501" 3 "j" "" 10 .08911946
"CMS Energy Corp CMS:NYQ" "CMS" 3 "u" "" 10 .08893535
"Vectren Corp VVC:NYQ" "VVC" 3 "u" "" 10 .08645996
"HK Electric Investments Ltd HKCVF:PNK" "HKCVF" 3 "u" "" 10 .08574605
"Energiekontor AG EKTX:GER" "EKTX" 3 "eu" "EKTX.DE" 10 .08558413
"Energiekontor AG EKTX:GER" "EKTX" 3 "eu" "EKTX.DE" 10 .08558413
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"YTL Corporation Bhd 1773.T:TYO" "1773.T" 3 "j" "" 10 .08398792
"China Datang Corp Renewable Power Co Ltd DT7:FRA" "DT7" 3 "eu" "DT7.F" 10 .08346357
end
local p 10
local f 1

set trace on 
levelsof site, local(sites)
foreach i of local sites {
levelsof StockCode if site == "`i'", local(stock`i')
}


gsort -p_Model1
count
local nobs `r(N)'
local stocks
forvalues i = 1/`nobs' {
local stocks `stocks' `=StockCode[`i']'
}

tempfile copy
foreach i of local sites {

*foreach s of local stock`i' {

foreach s of local stocks {

capture noisily {

summ site if StockCode == `"`s'"', meanonly
local site `r(mean)'
save `"`copy'"', replace

local f = `f'+1
use "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/STATA Data 5.dta", replace

merge 1:1 Date using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector`p'/Region`i'/`s'.dta", nogenerate

if c(rc) {
                continue
            }

tsset Date, daily

gsort -Date

local z `i'`s'

preserve
drop Date `z'
local x
foreach var of varlist *{
local x `x' `var'

}
restore

}
use `copy', clear
}

}

= version 11: _describe using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector10/Regionu/DT7.dta", varlist short
file /Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector10/Regionu/DT7.dta not found

[Clyde Schechter]

Looking at the data and the logic of your code, I think they are mismatched to each other. Your explanations have not been very clear, and I had thought that each of the StockCodes would cooccur with each site, but they do not. Most of the stock codes occur with only one site. None, at least in the example, co-occurs with all three sites. Your code corresponds to the assumption that each stock will co-occur with each site, so, yes you are getting inappropriate pairings, because that's precisely what the code was designed to do.

I think the whole logic of it needs to be reworked. It's not entirely clear what the logic of the rework should be, because I also notice that a combination of StockCode and site can occur more than once in the data set. With the code that you show, the implication of this is just that in these instances, the same work (whatever that may be) is repeated multiple times--which is just a waste of effort. So I'm going to assume that even if a stock-site combination appears more than once in the data, you only want to do the work on that pair once. That assumption greatly simplifies the code. If that assumption is wrong, then you are facing some additional, more complicated work.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str87 Stock str9 StockCode float region str2 site str10 exch long Sector1 float p_Model1
"Yu Group PLC YU.:LSE" "YU" 3 "eu" "YU.L" 10 1.9536498
"Empresa Distribuidora y Comercializadora Norte SA EDN:NYQ" "EDN" 3 "u" "" 10 .6723835
"Beijing Enterprises Water Group Ltd HUN2:FRA" "HUN2" 3 "eu" "HUN2.F" 10 .1943116
"American States Water Co AWR:NYQ" "AWR" 3 "u" "" 10 .19115694
"American States Water Co FDK:FRA" "FDK" 3 "eu" "FDK.F" 10 .190062
"Mercury NZ Ltd MGHTF:PNK" "MGHTF" 3 "u" "" 10 .187866
"Pembina Pipeline Corp P5P:FRA" "P5P" 3 "eu" "P5P.F" 10 .1841431
"Pembina Pipeline Corp. PBA:NYQ" "PBA" 3 "u" "" 10 .18383025
"ONEOK Inc OKE:NYQ" "OKE" 3 "u" "" 10 .18007235
"Aqua America Inc A2A:BER" "A2A" 3 "eu" "A2A.BE" 10 .1745505
"Aqua America Inc WTR:NYQ" "WTR" 3 "u" "" 10 .1743941
"Ultrapar Participacoes SA UGP:NYQ" "UGP" 3 "u" "" 10 .17302632
"Middlesex Water Co MSEX:NSQ" "MSEX" 3 "u" "" 10 .16821465
"New Jersey Resources Corp NJR:NYQ" "NJR" 3 "u" "" 10 .1586567
"California Water Service Group CWT:NYQ" "CWT" 3 "u" "" 10 .15459895
"TTW PCL F1P:FRA" "F1P" 3 "eu" "F1P.F" 10 .1545492
"California Water Service Group WT5:BER" "WT5" 3 "eu" "WT5.BE" 10 .153504
"Ormat Technologies Inc ORA:NYQ" "ORA" 3 "u" "" 10 .14909956
"Ormat Technologies Inc ORA:NYQ" "ORA" 3 "u" "" 10 .14909956
"Keppel Infrastructure Trust KPLIF:PNK" "KPLIF" 3 "u" "" 10 .13975017
"Enbw Energie Baden Wuerttemberg AG EBK:FRA" "EBK" 3 "eu" "EBK.F" 10 .13503219
"Dian Swastatika Sentosa Tbk PT DSX:BER" "DSX" 3 "eu" "DSX.BE" 10 .13276142
"Athena Investments A/S GRTSF:PNK" "GRTSF" 3 "u" "" 10 .12976353
"South Jersey Industries Inc SJI:NYQ" "SJI" 3 "u" "" 10 .12947209
"South Jersey Industries Inc J4S:FRA" "J4S" 3 "eu" "J4S.F" 10 .12790787
"Rubis SCA RUI:PAR" "RUI" 3 "eu" "RUI.PA" 10 .12787674
"Enel Americas SA ENIA:NYQ" "ENIA" 3 "u" "" 10 .12765475
"Athens Water and Sewerage Company SA EYP:BER" "EYP" 3 "eu" "EYP.BE" 10 .12760505
"Snam SpA SRG:MIL" "SRG" 3 "eu" "SRG.MI" 10 .12455654
"Enel Americas SA NER,R:MUN" "NER,R" 3 "eu" "NER,R.MU" 10 .1235878
"Chesapeake Utilities Corp CPK:NYQ" "CPK" 3 "u" "" 10 .12345844
"Chesapeake Utilities Corp CPK:NYQ" "CPK" 3 "u" "" 10 .12345844
"SJW Group SJW:NYQ" "SJW" 3 "u" "" 10 .12241271
"Chesapeake Utilities Corp CH5:FRA" "CH5" 3 "eu" "CH5.F" 10 .1223635
"Chesapeake Utilities Corp CH5:FRA" "CH5" 3 "eu" "CH5.F" 10 .1223635
"PGE Polska Grupa Energetyczna SA PPOEF:PNK" "PPOEF" 3 "u" "" 10 .12053046
"EDP Renovaveis SA EDPR:LIS" "EDPR" 3 "eu" "EDPR.LS" 10 .11502533
"Severn Trent PLC SVT:LSE" "SVT" 3 "eu" "SVT.L" 10 .11397702
"Iren SpA IRE:MIL" "IRE" 3 "eu" "IRE.MI" 10 .11255188
"Iren SpA TZ8:BER" "TZ8" 3 "eu" "TZ8.BE" 10 .11255188
"Tokai Holdings Corp 3167:TYO" "3167" 3 "j" "" 10 .11030423
"American Water Works Company Inc AWK:NYQ" "AWK" 3 "u" "" 10 .1074386
"Star Group LP SGU:NYQ" "SGU" 3 "u" "" 10 .10721345
"A2A SpA A2A:MIL" "A2A" 3 "eu" "A2A.MI" 10 .1070382
"2G energy AG 2GBX:GER" "2GBX" 3 "eu" "2GBX.DE" 10 .10557172
"Tallinna Vesi AS A1T:BER" "A1T" 3 "eu" "A1T.BE" 10 .10513643
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"Companhia Energetica de Minas Gerais CEMIG CIG:NYQ" "CIG" 3 "u" "" 10 .10292914
"WGL Holdings Inc WGL:NYQ" "WGL" 3 "u" "" 10 .09952857
"Pne Wind AG PNEX.N:GER" "PNEX.N" 3 "eu" "PNEX.N.DE" 10 .09937995
"UGI Corp UGI:NYQ" "UGI" 3 "u" "" 10 .09915674
"Acea SpA ACE:MIL" "ACE" 3 "eu" "ACE.MI" 10 .09829516
"NiSource Inc NI:NYQ" "NI" 3 "u" "" 10 .09591556
"Enel Generacion Chile SA EOCC:NYQ" "EOCC" 3 "u" "" 10 .09384745
"Enel SpA ENL:GER" "ENL" 3 "eu" "ENL.DE" 10 .0913777
"Enel SpA ENL:GER" "ENL" 3 "eu" "ENL.DE" 10 .0913777
"Alliant Energy Corp LNT:NYQ" "LNT" 3 "u" "" 10 .0912176
"National Fuel Gas Co NFG:NYQ" "NFG" 3 "u" "" 10 .090762
"Enel SpA ENEL:MIL" "ENEL" 3 "eu" "ENEL.MI" 10 .09075201
"Enel SpA ENEL:MIL" "ENEL" 3 "eu" "ENEL.MI" 10 .09075201
"Tokyo Electric Power Company Holdings Inc 9501:TYO" "9501" 3 "j" "" 10 .08911946
"CMS Energy Corp CMS:NYQ" "CMS" 3 "u" "" 10 .08893535
"Vectren Corp VVC:NYQ" "VVC" 3 "u" "" 10 .08645996
"HK Electric Investments Ltd HKCVF:PNK" "HKCVF" 3 "u" "" 10 .08574605
"Energiekontor AG EKTX:GER" "EKTX" 3 "eu" "EKTX.DE" 10 .08558413
"Energiekontor AG EKTX:GER" "EKTX" 3 "eu" "EKTX.DE" 10 .08558413
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:NYQ" "ELP" 3 "u" "" 10 .08544923
"Companhia Paranaense de Energia ELP:MUN" "ELP" 3 "eu" "ELP.MU" 10 .08544923
"YTL Corporation Bhd 1773.T:TYO" "1773.T" 3 "j" "" 10 .08398792
"China Datang Corp Renewable Power Co Ltd DT7:FRA" "DT7" 3 "eu" "DT7.F" 10 .08346357
end
local p 10
local f 1

levelsof site, local(sites)
foreach i of local sites {
    levelsof StockCode if site == "`i'", local(stock`i')
}


//    PACK THE STOCK-SITEs INTO A LOCAL MACRO IN ORDER
//    OF DECREASING P_MODEL1
gsort -p_Model1
count
local nobs `r(N)'
local stocks
forvalues i = 1/`nobs' {
    local stocks `"`stocks' `"`=StockCode[`i']'"'"'
}
//    REMOVE DUPLICATE MENTIONS OF THE SAME STOCK CODE
local stocks: list uniq stocks

tempfile copy


// LOOP OVER STOCK CODES
foreach s of local stocks {    
    //    LOOP OVER SITES ASSOCIATED WITH THIS STOCK CODE
    quietly levelsof site if StockCode == "`s'", local(sites)
    foreach i of local sites {
        

        capture noisily {
//    I HAVE COMMENTED OUT THESE LINES BECAUSE THEY DON'T MAKE SENSE.
//    SITE IS A STRING VARIABLE, NOT A NUMBER, SO YOU CAN'T -SUMM- IT.
//         summ site if StockCode == `"`s'"', meanonly
//         local site `r(mean)'
        save `"`copy'"', replace

        local f = `f'+1
        display `"`s' `i'"'
            use "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/STATA Data 5.dta", replace
    
            merge 1:1 Date using "/Users/gilesgerman/Documents/Stokpix/StokpixModels/Model2/Equities/Sector`p'/Region`i'/`s'.dta", nogenerate

        if c(rc) {
            continue
        }

        tsset Date, daily

        gsort -Date

        local z `i'`s'

        preserve
        drop Date `z'
        local x
        foreach var of varlist *{
            local x `x' `var'
        }
        restore

        use `copy', clear
    }
}

Note: Each time you post back code, all of the indentation that I put into it beforehand seems to disappear. The lack of indentation makes the code very difficult to read and follow, and I have probably spent as much time restoring the indentation to the code as I do figuring out how to fix the problems you point up. So please be sure to preserve the indentation going forward. If you have been deliberately removing it, please don't. If this is a side effect of passing the code through some text editor you are using on it, please adjust the settings of the text editor so it doesn't remove indentation, or, stop using the text editor and use the Stata do-file editor instead.