list of second foreach to vary according to value of first foreach

[Paula de Souza Leao Spinola]

I would like to write a code with two -foreach-, onde inside the other. However, I would like the list of the second foreach to vary acording to the value of the first foreach.

I need to change the second line below in order to make the list vary acording to `u'. Does anyone know how could I do so?

foreach u in SP MG {
foreach y of LIST VARYING ACORDING TO SP OR MG {
   foreach x in 1s 2s{
        use PAMG`y'_`x', clear

keep dt_proc cnes08 mun_cnes cod_proc08 qt_prod

gen tp_proc = 1 if inlist(cod_proc08,101010010,101030010,301010137,301050058)
replace tp_proc = 2 if inlist(cod_proc08,301010030,301010064)
replace tp_proc = 3 if cod_proc08==301010110
replace tp_proc = 4 if cod_proc08==310010012
drop cod_proc08

*mantendo apenas os procedimentos acima com municipio do cnes informado
drop if tp_proc==. | mun_cnes==.


save PA`u'`y'_`x'_temp
}
}
}

[Joseph Coveney]

Maybe something like

local SP_list a b c d
local MG_list e f g h
foreach u in SP MG {
    foreach y in ``u'_list' {
        . . .
    }
}

Otherwise, you could use

if "`u'" == "SP" {
    . . .
}
else if "`u'" == "MG" {
    . . .
}
else {
    <error message>
}

or cond("`u'" == "SP", ..., ...)

[Paula de Souza Leao Spinola]

Now I have a harder task within this same issue:

I have hospitalization datasets for 27 different states, for several years (2008-2014) and every month (01-12). The datasets names are called: PA'state''year''month'. However, specifically for years 2013 and 2014 and state of São Paulo (SP - biggest state in Brazil), I have two datasets for each month (a and b). So, they are called: PASP1301a, PASP1301b, PASP1302a, PASP1301b, etc. I would like to append all datasets for the same state and year, selecting only some variables. I would need to insert, in the code below, a fourth foreach - forach i in a b - specifically for the state of São Paulo ("`u'"=="SP") and years of 2013 or 2014 (`y'==13 or 14). How could I do so without rewriting the last commands which call the dataset, keep variables, append and save?

foreach u in AC    AL AP AM BA    CE DF ES GO    MA MT MS MG    PR PB PA PE    PI RJ RN RS    RO RR SC SE TO SP {
foreach y in 08 09 10 11 12 13 14 {
clear
tempfile temp
save `temp', emptyok
foreach m in 01 02 03 04 05 06 07 08 09 10 11 12 {
use "PA`u'`y'`m'", clear
keep pa_coduni pa_ufmun pa_tpups pa_cnpjcpf pa_mvm pa_cmp pa_proc_id pa_nivcpl pa_docorig pa_qtdpro
append using `temp'
save `"`temp'"', replace
}
save PA_`u'_`y'.dta, replace
}
}

[Clyde Schechter]

I think you're making this more complicated than it needs to be. Once you get down to the `y' level, you do not need to recompute all the names of the monthly files, whether there are a's and b's or not. You can have Stata find all the files that begin with PA`u'`y' and append them:

foreach u in AC    AL AP AM BA    CE DF ES GO    MA MT MS MG    PR PB PA PE    PI RJ RN RS    RO RR SC SE TO SP {
    foreach y in 08 09 10 11 12 13 14 {
        clear
        tempfile temp
        save `temp', emptyok
        local filenames: dir "." files "PA`u'`y'*.dta"
        foreach f in local filenames{
            use "`f'", clear
            keep pa_coduni pa_ufmun pa_tpups pa_cnpjcpf pa_mvm pa_cmp pa_proc_id pa_nivcpl pa_docorig pa_qtdpro
            append using `temp'
            save `"`temp'"', replace
        }
    save PA_`u'_`y'.dta, replace
    }
}

This will be robust to additional complexity in the file names. The only problem I can foresee with this approach is if there are extraneous files named PA`u'`y'*.dta that you do not wish to include in the big append.

[Paula de Souza Leao Spinola]

Thank you, Clyde!
I had the problem below.

. foreach u in AC {
  2.     foreach y in 08 09 10 11 12 13 14 {
  3.         clear
  4.         tempfile temp
  5.         save `temp', emptyok
  6.         local filenames: dir "." files "PA`u'`y'*.dta"
  7.         foreach f in local filenames{
  8.             use "`f'", clear
  9.             keep pa_coduni pa_ufmun pa_tpups pa_cnpjcpf pa_mvm pa_cmp pa_proc_id pa_nivcpl pa_docorig pa_qtdpro
 10.             append using `temp'
 11.             save `"`temp'"', replace
 12.         }
 13.     save PA_`u'_`y'.dta, replace
 14.     }
 15. }
(note: dataset contains 0 observations)
file C:\Users\Paula\AppData\Local\Temp\ST_0f000001.tmp saved
file local.dta not found
r(601);

end of do-file

r(601);

[Clyde Schechter]

Sorry, my mistake. Should be -foreach f of local filenames-, not -in local filenames- Also, we need a blank space between filenames and {.

Sorry about that.

[Paula de Souza Leao Spinola]

Thank you! Now I get error r(111). Would you know why?

. foreach u in AC {
  2.     foreach y in 08 09 10 11 12 13 14 {
  3.         clear
  4.         tempfile temp
  5.         save `temp', emptyok
  6.         local filenames: dir "." files "PA`u'`y'*.dta"
  7.         foreach f of local filenames  {
  8.             use "`f'", clear
  9.             keep pa_coduni pa_ufmun pa_tpups pa_cnpjcpf pa_mvm pa_cmp pa_proc_id pa_nivcpl pa_docorig pa_qtdpro
 10.             append using `temp'
 11.             save `"`temp'"', replace
 12.         }
 13.     save PA_`u'_`y'.dta, replace
 14.     }
 15. }
(note: dataset contains 0 observations)
file C:\Users\Paula\AppData\Local\Temp\ST_0f000001.tmp saved
no variables defined
r(111);

end of do-file

r(111);

[Clyde Schechter]

One of the files that your loop attempts to read contains no variables. If one file is bad, there is the possibility that others will be, too. I suggest scanning all of your files for this problem:

local filenames: dir "." files "PAAC*.dta"
foreach f of local filenames {
    display `"`filename'"'
    quietly des
    if r(k) == 0 {
        display in red "No variables in this file"
    }
    if r(N)== 0 {
        display in red "No observations in this file"
    }
    display
}

This will list out all the PAAC*.dta files and identify for you any which have either no variables or no observations. Then you can trace back the origins of those files to find out why they are empty.

[Clyde Schechter]

Actually, even better would be this:

local filenames: dir "." files "PAAC*.dta"
foreach f of local filenames {
    display `"`filename'"'
    quietly des
    if r(k) == 0 {
        display in red "No variables in this file"
    }
    else {
        foreach v in pa_coduni pa_ufmun pa_tpups pa_cnpjcpf pa_mvm ///
            pa_cmp pa_proc_id pa_nivcpl pa_docorig pa_qtdpro  {
                capture confirm var `v'
                if c(rc) != 0 {
                    dislpay in red "Variable `v' not in this file"
                }
        }
    }
    if r(N)== 0 {
        display in red "No observations in this file"
    }
    display
}

This one will also alert you to a file that does not have all of the variables that you want to -keep-.

[Paula de Souza Leao Spinola]

This is very strange, because the files which I am calling do have variables... In the code below I called the first file of the looping to make sure:

. use PAAC0801, clear

. des

Contains data from PAAC0801.dta
  obs:        23,878                          
 vars:            54                          8 May 2015 02:43
 size:     5,253,160                          
------------------------------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------------------------------------------
pa_coduni       str7    %7s                   
pa_gestao       str6    %6s                   
pa_condic       str2    %2s                   
pa_ufmun        str6    %6s                   
pa_regct        str4    %4s                   
pa_incout       str4    %4s                   
pa_incurg       str4    %4s                   
pa_tpups        str2    %2s                   
pa_tippre       str2    %2s                   
pa_mn_ind       str1    %1s                   
pa_cnpjcpf      str14   %14s                  
pa_cnpjmnt      str14   %14s                  
pa_cnpj_cc      str14   %14s                  
pa_mvm          str6    %6s                   
pa_cmp          str6    %6s                   
pa_proc_id      str10   %10s                  
pa_tpfin        str2    %2s                   
pa_subfin       str4    %4s                   
pa_nivcpl       str1    %1s                   
pa_docorig      str1    %1s                   
pa_autoriz      str13   %13s                  
pa_cnsmed       str15   %15s                  
pa_cbocod       str6    %6s                   
pa_motsai       str2    %2s                   
pa_obito        str1    %1s                   
pa_encerr       str1    %1s                   
pa_perman       str1    %1s                   
pa_alta         str1    %1s                   
pa_transf       str1    %1s                   
pa_cidpri       str4    %4s                   
pa_cidsec       str4    %4s                   
pa_cidcas       str4    %4s                   
pa_catend       str2    %2s                   
pa_idade        str3    %3s                   
idademin        str2    %2s                   
idademax        str3    %3s                   
pa_flidade      str1    %1s                   
pa_sexo         str1    %1s                   
pa_racacor      str2    %2s                   
pa_munpcn       str6    %6s                   
pa_qtdpro       long    %12.0g                
pa_qtdapr       long    %12.0g                
pa_valpro       double  %10.0g                
pa_valapr       double  %10.0g                
pa_ufdif        str1    %1s                   
pa_mndif        str1    %1s                   
pa_dif_val      byte    %8.0g                 
nu_vpa_tot      byte    %8.0g                 
nu_pa_tot       float   %9.0g                 
pa_indica       str1    %1s                   
pa_codoco       str1    %1s                   
pa_flqt         str1    %1s                   
pa_fler         str1    %1s                   
pa_etnia        str1    %1s                   
------------------------------------------------------------------------------------------------------------------------------------
Sorted by:

So, I don't know why this code tells us there is no variable in such file PAAC0801.dta. Would you happen to know?

. foreach u in AC {
  2.     foreach y in 08 09 10 11 12 13 14 {
  3.         clear
  4.         tempfile temp
  5.         save `temp', emptyok
  6.         local filenames: dir "." files "PA`u'`y'*.dta"
  7.         foreach f of local filenames  {
  8.             use "`f'", clear
  9.             keep pa_coduni pa_ufmun pa_tpups pa_cnpjcpf pa_mvm pa_cmp pa_proc_id pa_nivcpl pa_docorig pa_qtdpro
 10.             append using `temp'
 11.             save `"`temp'"', replace
 12.         }
 13.     save PA_`u'_`y'.dta, replace
 14.     }
 15. }
(note: dataset contains 0 observations)
file C:\Users\Paula\AppData\Local\Temp\ST_0f000001.tmp saved
no variables defined
r(111);

end of do-file

r(111);

[Paula de Souza Leao Spinola]

The problem is that this - foreach f of local filenames- is not working. I do not know why.

. local filenames: dir "." files "PAAC*.dta"

. foreach f of local filenames {
  2.     display `"`filename'"'
  3.     quietly des
  4.     if r(k) == 0 {
  5.         display in red "No variables in this file"
  6.     }
  7.     if r(N)== 0 {
  8.         display in red "No observations in this file"
  9.     }
 10.     display
 11. }

. 
end of do-file

.

[William Lisowski]

If you have not done so, you might try including

pwd
dir .
display `"`filenames'"'

before the loop to see if you are in the directory you expect you are in, to see what files are there, and to see if your filenames macro was set correctly. This is fairly elementary problem-solving that is always a good first step in situations like this.

[Robert Picard]

The syntax to display variables in a dataset instead of variables in memory is

describe [varlist] using filename [, file_options]

[Clyde Schechter]

Yes, Robert is right; my mistake. -quietly des- in #11 should have been -quietly des using `f'-. Sorry for that.

[Paula de Souza Leao Spinola]

I am confused. What should I do differently in the code below?
ps: I confirmed all files have variables, the error below is due to the -foreach f of local filenames- not working properly. In this case Stata tries to keep variables from the empty file, created in the beginning of the code.

. foreach u in AC {
  2.     foreach y in 08 09 10 11 12 13 14 {
  3.         clear
  4.         tempfile temp
  5.         save `temp', emptyok
  6.         local filenames: dir "." files "PA`u'`y'*.dta"
  7.         foreach f of local filenames  {
  8.             use "`f'", clear
  9.             keep pa_coduni pa_ufmun pa_tpups pa_cnpjcpf pa_mvm pa_cmp pa_proc_id pa_nivcpl pa_docorig pa_qtdpro
 10.             append using `temp'
 11.             save `"`temp'"', replace
 12.         }
 13.     save PA_`u'_`y'.dta, replace
 14.     }
 15. }
(note: dataset contains 0 observations)
file C:\Users\Paula\AppData\Local\Temp\ST_0f000001.tmp saved
no variables defined
r(111);

end of do-file

r(111);

.