Combination of foreach, regress and egen

[Thomas Kean]

Hello Forum-members,

Problem:
based on a panel data set i want to make a regression where i regress working capital (delta_wc) (dependent variable) on cashflow t-1 (cfom1), cashflow t=0 (cfo0), cashflow t+1 (cfop1) and revenues (delta_rev) [independent variables].

My data set looks like this:

Deal_no T-X delta_wc Delta_CurrAss Delta-CurrLiab Delta_Cash cfom1 cfo0 cfop1 delta_rev 2600017020 T-2 -0,0180413 0,031671124 0,06542003 -0,015707609 0,1724535 0,197512 0,17941907 0,091121965 2600017020 T-3 -0,0581152 0,056857121 0,076703786 0,038268518 0,1201226 0,172454 0,19751155 0,144121525 2600017020 T-4 -0,0040045 0,109381194 0,032665025 0,080720668 0,07964999 0,120123 0,1724535 0,130216808 2600718020 T-2 0,06306802 0 0 -0,063068016 0,12407959 -0,00038 -0,0163338 -0,009135859 2600718020 T-3 -0,0570291 0 0 0,057029101 -0,0657198 0,12408 -0,0003798 -0,008206522 2600718020 T-4 0,02466135 0 0 -0,024661349 0,05740337 -0,06572 0,12407959 -0,016067139 ....

However, i do not want to just regress (reg) delta_wc on the dependent variables mentioned above, but rather i want to make this regression for each deal no (three observation per one deal number) (foreach). --> in the context of this example, i would like to have two regressions equations, one for 2600017020 and one for2600718020.
After that, i would like to calculate for each deal number and observation (t-2. t-3, t-4) the delta_wc value estimated from the regression equation (egen) (Reason: I want to be able to calculate the residuals).

Now, i have the following question:
Is there a way which allows me to combine the reg command with foreach & egen?

Thank you very much in advance and kind regards!

[Ken Chui]

I don't think egen is necessary, just use predict after the regression. Here is some sample codes:

sysuse auto, clear

gen my_res = .
levelsof foreign, local(lv)
foreach x in `lv'{
    reg mpg price weight headroom if foreign == `x'
    capture drop temp_yhat
    predict temp_yhat if foreign == `x'
    replace my_res = max(my_res, temp_yhat) if foreign == `x'
}

For your case, foreign will be "Deal_nl". And replace the regression with whatever you're using and it should work.

Also, it could just be your example being simplified. If I understand your analysis correctly, it's not feasible to have 4 predictors for 3 cases. The model will not run.

[Thomas Kean]

Thank you for the very quick response.

Indeed, you are right: With the dataset i provided, it would not be feasible to run the regression - thank you for the hint. Now, i just regress delta_wc on cfom1, cfo0 and cfop1 (three variables and three observations).

However, when i run the regression stata shows an error message "type mismatch". The error indicates that i would try to combine a string and a numeric expression. But by defining my_res = . i am generating a numeric variable that i want to replace with a numeric regression output, right? Do you have an idea, why this error message pops up? I tried typing my_res = "" but this led to the same error message.

And could you explain why the line "capture drop temp_yhat" is included? (How can i drop something that i have not defined before [i am not trying to question you answer, i just want to understand what i am including in my stata code].

Again, thank you in advance for your help

My current code:
gen my_res = .
(222 missing values generated)

. levelsof deal_no, local(lv_deal_no)
`"2600017020"' `"2600718020"' `"2609064020"' `"2630424020"' `"2648719020"' `"2654961020"' `"2656648020"' `"2663270020"' `"2667598020"' `"2682083020"' `"2714224020"' `"272713802
> 0"' `"2734242020"' `"2748372020"' `"2749664020"' `"2749745020"' `"2778456020"' `"2789583020"' `"2814327020"' `"2816892020"' `"2821391020"' `"2834870020"' `"2843425020"' `"289
> 9175020"' `"2924772020"' `"2941412020"' `"2954443020"' `"2965097020"' `"2966688020"' `"2968608020"' `"2980060020"' `"3018423020"' `"3020752020"' `"3030956020"' `"3043982020"'
> `"3055427020"' `"3056110020"' `"3060281020"' `"3065264020"' `"3065880020"' `"3088265020"' `"3124106020"' `"3148976020"' `"3172585020"' `"3198363020"' `"3202858020"' `"320318
> 2020"' `"3213109020"' `"3218277020"' `"3219995020"' `"3228782020"' `"3241705020"' `"3245544020"' `"3245551020"' `"3252628020"' `"3286029020"' `"3288419020"' `"3290193020"' `"
> 3291789020"' `"3301353020"' `"3307458020"' `"3308159020"' `"3309331020"' `"3310138020"' `"3317549020"' `"3320448020"' `"3330781020"' `"3331129020"' `"3345697020"' `"334849902
> 0"' `"3375613020"' `"3445980020"' `"3473674020"' `"3476467020"'

. foreach x in `lv_deal_no' {
2. reg delta_wc cfom1 cfo0 cfop1 if deal_no == `x'
3. capture drop temp_yhat
4. predict temp_yhat if foreign == `x'
5. replace my_res = max(my_res, temp_yhat) if deal_no == `x'
6. }
type mismatch
r(109)

[Nick Cox]

At a guess your loop should be more like

foreach x in `lv_deal_no' {
reg delta_wc cfom1 cfo0 cfop1 if deal_no == "`x'"
capture drop temp_yhat
predict temp_yhat if deal_no == "`x'"
replace my_res = max(my_res, temp_yhat) if deal_no == "`x'"
}

The guess here is several guesses:

1. If deal_no is string, you'll get a type mismatch but the double quotes should fix that.

2. foreign copied from @Ken Chui's code needs to be deal_no

A regression with 3 predictors and 3 observations is still not sensible.

capture drop arises in two ways. We need to drop the variable before we use predict. But first time around the loop it does not yet exist. The capture catches that. predictable error and just eats it.

[Thomas Kean]

Thank you again. Sorry, i should have seen that there was still "foreign" in my code. The regression works now but omits one predictor. I guess, that cannot run the regression individually by deal_no then but have to run the regression on my entire data set.
Thank you all for your very helpful support!