Problem when trying to import excel into stata with loop

[Wenhan Yan]

I am trying to import state unemployment into stata and then append them all into one file. Here is what I try to do with loop, and original data and final data are in different dataset. But I encounter error as "no; data in memory would be lost"

clear

set more off


global path "D:\OneDrive..."



cd "$path"

* state abb list
global abb "AL AK AZ AR CA CO CT DE FL GA HI ID IL IN IA KS KY LA ME MD MA MI MN MS MO MT NE NV NH NJ NM NY NC ND OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY"

foreach x of global abb {
    import excel "data/monthly unemployment rate/`x'.xlsx", sheet("BLS Data Series") cellrange(A11:H71) firstrow





    gen month = month(date(Period, "M"))


    rename Year year
    gen date =ym(year,month)
    format date %tmCCYY/NN

    *gen state variable
    gen state_abb = "`x'"

    save "data_final/`x'.dta", replace
}

And following is what I thought about the appending, but have not try yet

use "data_final/AL.dta", replace
global app "AK AZ AR CA CO CT DE FL GA HI ID IL IN IA KS KY LA ME MD MA MI MN MS MO MT NE NV NH NJ NM NY NC ND OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY"

foreach y of global app{
    
    
    append using "data_final/`y.dta"
    
    
}

save "data_final/2018_2022 unemploy.dta", replace

[Clyde Schechter]

I don't understand why you are getting that error message, given that the loop saves the data at the end of each iteration. Nevertheless, to just get around this, you can add a -clear- option to the -import excel- command, or put a -clear- command at the top of the loop.

As for your proposed appending loop, there is a small typo. The -append-command should be:

append using "data_final/`y'.dta"

Also, you don't have to do an explicit loop to append these all together. You could instead do:

clear
local filenames: dir "data_final" files "??.dta"
append using `filenames'
save "data_final/2018_2022 unemploy.dta", replace

[Wenhan Yan]

I don't understand why you are getting that error message, given that the loop saves the data at the end of each iteration. Nevertheless, to just get around this, you can add a -clear- option to the -import excel- command, or put a -clear- command at the top of the loop.

As for your proposed appending loop, there is a small typo. The -append-command should be:

append using "data_final/`y'.dta"

Also, you don't have to do an explicit loop to append these all together. You could instead do:

clear
local filenames: dir "data_final" files "??.dta"
append using `filenames'
save "data_final/2018_2022 unemploy.dta", replace

Hello Clyde,

Thank you so much, by adding the clear the loop does work, and thank you for pointing out the error.

When I try to use the code you provide, there is an error code:

file ak.dta not found

It is actually the first time I use dir in local variable, so I don't quite sure why it doesn't work. So I use the explicit loop to do so

Thanks again for the solution

[Clyde Schechter]

When I try to use the code you provide, there is an error code:

file ak.dta not found

I see the problem. The -local filenames- command searches in the data_final directory: but it does not apply the pathname to the filename. The code then comes back and tries to run the -append using- command. But the working directory for running the code is, I presume, something other than the data_final directory. So it's trying to find ak.dta in the wrong directory. What I should have told you to do is:

clear
local come_back_here `c(pwd)'
cd data_final
local filenames: dir "." files "??.dta"
append using `filenames'
save "2018_2022 unemploy.dta", replace
cd "`come_back_here'"

[Wenhan Yan]

I see the problem. The -local filenames- command searches in the data_final directory: but it does not apply the pathname to the filename. The code then comes back and tries to run the -append using- command. But the working directory for running the code is, I presume, something other than the data_final directory. So it's trying to find ak.dta in the wrong directory. What I should have told you to do is:

clear
local come_back_here `c(pwd)'
cd data_final
local filenames: dir "." files "??.dta"
append using `filenames'
save "2018_2022 unemploy.dta", replace
cd "`come_back_here'"

Hello Clyde,

Thank you so much, it works very well.