st: Re: st: A “loop” to merge dataset with a different file each time?

[Phil Clayton <philclayton@internode.on.net>]

Your basic approach is to use a -forvalues- (or -foreach-) loop. See -help forvalues-. You also only need to -insheet- the master once, and I combined the -generate- commands to save a bit of time.

-----------------------
insheet using "C:\ DATA\uncertainty analysis\for meging.txt", clear 
sort foodcode 
save "C:\DATA\uncertianity analysis\master.dta", replace 

forvalues i=1/1000 {
	insheet using "C:\DATA\uncertianity analysis\foodpricedata_`i'.txt" , clear 
	merge m:1 foodcode using "C:\DATA\uncertianity analysis\master.dta"
	generate Tghgecode = real( totalpricekg) * numberofportions
	total  Tghgecode
}
-----------------------

The next step would be to collate the results from these 1000 operations in a neat way. There are a few ways to do this. Probably worth testing the above code (eg in the first 20 datasets) before doing that.

Phil

On 5 Feb 2014, at 4:06 am, Kremlin Wickramasinghe <kremlin.wickramasinghe@dph.ox.ac.uk> wrote:

> Dear all, 
>  I want to merge two datasets and run an analysis 1000 times in a loop. I want to write a do file to automate this analysis. 
> 
> My master dataset has nutrition information of different foods (with a unique food code for each food name). I want to merge it with another dataset which contain food price data for those food codes. I have food prices from 1000 different stores in 1000 different Excel sheets. I have saved them with filenames with a sequence from 1-1000. (foodpricedata_1 to foodpricedata_1000) .   I want to  write a do file to automate the process of 
> a)      Do m:1 merge using foodcode , but each time use a different food price data file. 
> b)      Run the analysis 
> 
> The following do file shows the steps of running this analysis twice, first time  with the foodpricedata_1 and second time with the foodpricedata_2 files. I want to automate this to run 1000 times but each time using a unique file. I have seen tutorial on how to run a loop, but couldn’t find any resources on how to make it a loop using a different file in each time .  I would be most grateful if someone could guide me on how to do this. 
> 
> Do File : 
> 
> insheet using "C:\ DATA\uncertainty analysis\for meging.txt", clear 
> sort foodcode 
> save "C:\DATA\uncertianity analysis\master.dta", replace 
> 
> insheet using "C:\DATA\uncertianity analysis\foodpricedata_1.txt" , clear 
> save "C:\DATA\uncertianity analysis\working.dta", replace 
> sort foodcode
> merge m:1 foodcode using "C:\DATA\uncertianity analysis\master.dta",
> 
> *** Analysis***
> generate totalghgeR = real( totalpricekg)
> gen Tghgecode =  totalghgeR *  numberofportions
> total  Tghgecode
> 
> 
> clear 
> 
> insheet using "C:\DATA\uncertianity analysis\for meging.txt", clear 
> sort foodcode 
> save "C:\DATA\uncertianity analysis\master.dta", replace 
> 
> insheet using "C:\DATA\uncertianity analysis\ foodpricedata_2.txt" , clear 
> save "C:\DATA\uncertianity analysis\working.dta", replace 
> sort foodcode
> merge m:1 foodcode using "C:\DATA\uncertianity analysis\master.dta",
> 
> *** Analysis***
> generate totalghgeR = real( totalpricekg)
> gen Tghgecode =  totalghgeR *  numberofportions
> total  Tghgecode
> 
> 
> clear
> 
> End of Do file …..
> 
> Best wishes
> Kremlin 
> 
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/