Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Loop that imports/edit/save excel files, error: generated "variable already exists"

    Dear Statalist forum,

    I am running a loop to open all excel files inside a folder, make calculations and save the data as new excel files in a separate folder. However, I get an error now that my generated variable already exists after first loop, so only the first file is being processed.
    I have attempted to use drop preserve/restore and both together but is does not solve my problem.

    Can anyone here help me to identify the problem? I am truly grateful for your time

    Code:
    **Loop with other solution to loop
    
    
    * change path to where the folder with the raw xlsx that we want to change (Folder A)
    cd $RAW
    
    * We will open the excel files from folder A and save the edited excel files in Folder B (export folder)
    
    set trace on
    local xlsx : dir . files "*.xlsx"
    local xlsx : subinstr local xlsx ".xlsx" "", all
    foreach f of local xlsx {    
     
       di "`f'"
        import excel using `f', firstrow clear
        preserve
        *Calculate our variables:
    
        * 1)Sensitivity with 95% CI
        *first we convert Sensitivity back to fraction
        gen sens = Sensitivity/100
        export excel using "$EXPORT\newest_`f'", firstrow(variables) replace
        
        drop sens
        
        restore
    }
    Output:
    Code:
    variable sens already defined

    *Im using Stata 17 on a PC with this code

  • #2
    try

    Code:
    replace Sensitivity = Sensitivity/100

    Comment


    • #3
      I'm not sure I understand what you were trying to accomplish with drop/preserve/restore. Let me just start by simulating your situation by writing 5 workbooks with a sensitivity variable to the file system.

      Code:
      forv num = 1/5 {
          clear
          set obs 1000
          gen Sensitivity = runiform(0, 100)
          export excel using "workbook_`num'.xlsx", firstrow(variables) replace
      }
      clear
      In that case, the following works as expected and without error:

      Code:
      local xlsx : dir . files "*.xlsx"
      //local xlsx : subinstr local xlsx ".xlsx" "", all
      foreach f of local xlsx {
          di "`f'"
          import excel using `f', firstrow clear
          gen sens = Sensitivity/100
          export excel using "newest_`f'", firstrow(variables) replace
      }
      So you were already essentially there. I've modified this so it writes the new files to the same folder (the working directory) but I don't see why you shouldn't be able to write them somewhere else with $EXPORT. If you're getting an error that says sens already exists, that means one of two things: the sens variable already exists in that particular excel file independent of anything you've done here, or on a previous attempt at programing this loop you wrote the data to the same location as the input file, so you hammered over the data in the original files. In the second case, just go through each excel file and delete the sens variable. Hopefully it is only present in the first file, but if not, you can do that within the loop. You just have to drop sens after the file is loaded but before you try to generate it.

      Code:
      foreach f of local xlsx {
          di "`f'"
          import excel using `f', firstrow clear
          capture drop sens
          gen sens = Sensitivity/100
          export excel using "newest_`f'", firstrow(variables) replace
      }
      Right now you are doing this the correct way: By writing the modified data to a completely different file. Keep doing that and you should avoid errors like this in the future. #2 would probably work (you would just avoid any reference to sens in the first place), but I thought you might find a bit more explanation helpful.

      Note: I'm not sure I see any reason to strip the .xlsx extension off of each of these file names unless you want to save the data in the outdated .xls format. That's why I comment it out above.
      Last edited by Daniel Schaefer; 10 Nov 2023, 14:10.

      Comment

      Working...
      X