Looping through data files and writing the variables into excel

[Dann Morgan]

Afternoon,

I am trying to loop through a list of data files, with the aim of writing the list of the variables for each of these datasets into an excel spreadsheet. All the files have been downloaded into one of my folders. It seems that I am not able to specify the dataset correctly. I am using Stata 14.

The data files are from a panel survey. There are a number of modules for each of the years the survey was conducted. For example: year a consists of modules xxx yyy zzz; similarly year b consists of modules xxx yyy zzz.

The naming structure of the datasets is along the lines of: axxx ayyy azzz bxxx byyy bzzz .... Where a / b refers to the year of the survey and xxx/yyy/zzz the name of the module.

The code I have been using unsuccessfully is below. While it appears that I am unable to specify the correct the file which Stata should use - I have included my code for completeness. Apologies if this is too much information.

    local module a b c d e f
    foreach mod of local module {

        local coln = 66
        local colv = 66
        
        putexcel set "C:\Desktop\Output\Modulestest.xlsx", sheet ("`mod'") modify
        
        local datapref 1 2 3 4
        foreach dp of local datapref{
    
* Specify dataset

            local file "`dp'`mod'"
            disp "`file'"
            
            use C:\Desktop\Data\"`file'", clear

            qui ds, det
                local count: word count `r(varlist)'
                disp "Number of variables: `count'"
            
* Write data module and number of variables into excel 
            
            putexcel `=char(`coln')'2 = "`file'", font(calibri, 9)
            local coln = `coln' + 1
            putexcel `=char(`coln')'2 = "`count'", font(calibri, 9)
            local coln = `coln' + 6
                     
   * Write variables module into excel 
         
            descsave, saving(autodesc.dta, replace)
                use autodesc
             
            export excel using "C:\Desktop\Output\Modulestest.xlsx", ///
                sheet("`mod'") cell(char(`colv')4) sheetmodify 
                
            local colv = `colv'+ 7
            disp "char(`colv') char(`coln')"
        }
    }

When I run the above code I get an error message:
invalid '"1a'
r(198)


Any help would be much appreciated.

Thanks,

[Roger Chu]

Hello Dann,

I believe the error you get comes from

use C:\Desktop\Data\"`file'", clear

The quotation marks partway through the file path gives you that error.

Try

use "C:\Desktop\Data/`file'", clear

You'll need a forward slash before the macro-call characters, too. Don't worry, Stata doesn't care if you use forward or backward slashes in file paths.

[Dann Morgan]

Thanks Roger - your suggestion did the trick!