Collapsing Data without loosing the data

[Paul Halm]

Hello,

I have data on returns for different industries within different countries. I have wage returns, which I try to regress in an OLS regression on stock returns. The twist is that I want to regress the wage returns from one country and industry against stock returns of the same country but all available industries.

I tried by creating a new variable for each Wage and Stock return per industry:

gen WR_Q_Ger_Fin=WR_Q if country=="Germany" & Sector=="Finance, Insurance, Real Estate"

gen Ex_Ret_Ger_Min=Excess_Return if country=="Germany" & Sector=="Mining"

However then they are not in one observation, and a regression yields "no observations". I want to collapse the data, but I would loose data I need because I want to do this regression for Wage return in every single industry separately on stock returns of all the other industries.

Is there a solution to my problem?

Any help is appreciated. Attached you find my copied data from dataex.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte Quarter str24 country str34 Sector double Year float(WR_Q Excess_Returns Ex_Ret_Ger_Min WR_Q_Ger_Min)
1 "" "Transport, Utility, Communication " .     .04918033 . . .
1 "" "Retail Trade "                      .     .05405406 . . .
1 "" "Transport, Utility, Communication " .   -.010936432 . . .
1 "" "Transport, Utility, Communication " . -.00049194443 . . .
1 "" "Retail Trade "                      .     .04473304 . . .
1 "" "Construction"                       .             . . . .
1 "" "Retail Trade "                      .    -.01051051 . . .
1 "" "Mining"                             .     -.2480813 . . .
1 "" "Transport, Utility, Communication " .     -.2381847 . . .
1 "" "Retail Trade "                      .   .0004020909 . . .
1 "" "Mining"                             .     -.2696026 . . .
1 "" "Retail Trade "                      .     .00561555 . . .
1 "" "Transport, Utility, Communication " .    .022488756 . . .
1 "" "Retail Trade "                      .    -.26387832 . . .
1 "" "Mining"                             .    -.25214556 . . .
1 "" "Retail Trade "                      .    -.26385936 . . .
1 "" "Retail Trade "                      .     .05160281 . . .
1 "" "Transport, Utility, Communication " .   -.009595438 . . .
1 "" "Retail Trade "                      .      .0254372 . . .
1 "" "Wholesale Trade"                    .    .064599484 . . .
1 "" "Retail Trade "                      .    .034065936 . . .
1 "" "Transport, Utility, Communication " .    -.23392317 . . .
1 "" "Retail Trade "                      .    .012785388 . . .
1 "" "Retail Trade "                      .    .010253906 . . .
1 "" "Transport, Utility, Communication " .    -.22783847 . . .
1 "" "Transport, Utility, Communication " .    .003633561 . . .
1 "" "Retail Trade "                      .    .028673835 . . .
1 "" "Retail Trade "                      .     .01046025 . . .
1 "" "Retail Trade "                      .     .03275862 . . .
1 "" "Transport, Utility, Communication " .    .034428794 . . .
1 "" "Transport, Utility, Communication " .    -.23810093 . . .
1 "" "Transport, Utility, Communication " .    .018739816 . . .
1 "" "Transport, Utility, Communication " .   -.004448952 . . .
1 "" "Transport, Utility, Communication " .   .0046547507 . . .
1 "" "Mining"                             .  -.0041536936 . . .
1 "" "Retail Trade "                      .     .05323194 . . .
1 "" "Mining"                             .      .0881398 . . .
1 "" "Retail Trade "                      .    -.25892168 . . .
1 "" "Mining"                             .     .22542647 . . .
1 "" "Transport, Utility, Communication " .   -.005855444 . . .
1 "" "Retail Trade "                      .    -.26396543 . . .
1 "" "Mining"                             .    -.24498886 . . .
1 "" "Transport, Utility, Communication " .    .003617685 . . .
1 "" "Retail Trade "                      .    .028052805 . . .
1 "" "Retail Trade "                      .     .02662722 . . .
1 "" "Transport, Utility, Communication " .     .02835922 . . .
1 "" "Retail Trade "                      .    .035658915 . . .
1 "" "Transport, Utility, Communication " .     .00992823 . . .
1 "" "Transport, Utility, Communication " .     .03409411 . . .
1 "" "Retail Trade "                      .   -.006331618 . . .
1 "" "Mining"                             .    .030790906 . . .
1 "" "Mining"                             .     .09102208 . . .
1 "" "Retail Trade "                      .    -.26229742 . . .
1 "" "Retail Trade "                      .    -.26421696 . . .
1 "" "Mining"                             .    -.24495323 . . .
1 "" "Mining"                             .     -.2438466 . . .
1 "" "Transport, Utility, Communication " .    .009340162 . . .
1 "" "Retail Trade "                      .    .003726708 . . .
1 "" "Transport, Utility, Communication " .     -.0567024 . . .
1 "" "Retail Trade "                      .    .005323869 . . .
1 "" "Mining"                             .     .07746806 . . .
1 "" "Transport, Utility, Communication " .     .04652015 . . .
1 "" "Retail Trade "                      .     .08147175 . . .
1 "" "Mining"                             .   -.006023726 . . .
1 "" "Transport, Utility, Communication " .    .017791584 . . .
1 "" "Mining"                             .     -.2251042 . . .
1 "" "Retail Trade "                      .     .06330319 . . .
1 "" "Mining"                             .     -.2410232 . . .
1 "" "Transport, Utility, Communication " .    .004657933 . . .
1 "" "Transport, Utility, Communication " .   -.015272132 . . .
1 "" "Transport, Utility, Communication " .      .0361874 . . .
1 "" "Mining"                             .     .12531526 . . .
1 "" "Mining"                             .    -.10022112 . . .
1 "" "Retail Trade "                      .   -.014817367 . . .
1 "" "Mining"                             .      .2841784 . . .
1 "" "Retail Trade "                      .    .010043669 . . .
1 "" "Retail Trade "                      .   -.008100445 . . .
1 "" "Transport, Utility, Communication " .   -.003027027 . . .
1 "" "Transport, Utility, Communication " .     .03051997 . . .
1 "" "Transport, Utility, Communication " .   -.006662257 . . .
1 "" "Retail Trade "                      .    .021879483 . . .
1 "" "Retail Trade "                      .     -.0405904 . . .
1 "" "Mining"                             .      .0105573 . . .
1 "" "Transport, Utility, Communication " .    -.22855733 . . .
1 "" "Construction"                       .    -.05259888 . . .
1 "" "Transport, Utility, Communication " .   .0010711637 . . .
1 "" "Retail Trade "                      .     .05534105 . . .
1 "" "Transport, Utility, Communication " .   -.014279776 . . .
1 "" "Retail Trade "                      .      -.263818 . . .
1 "" "Transport, Utility, Communication " .    .005119255 . . .
1 "" "Retail Trade "                      .     .01794105 . . .
1 "" "Mining"                             .    -.05109156 . . .
1 "" "Retail Trade "                      .     .06551724 . . .
1 "" "Retail Trade "                      .     .04132231 . . .
1 "" "Transport, Utility, Communication " .    -.22398606 . . .
1 "" "Transport, Utility, Communication " .   .0006817407 . . .
1 "" "Transport, Utility, Communication " .     .04854927 . . .
1 "" "Transport, Utility, Communication " .         .0248 . . .
1 "" "Retail Trade "                      .     .03325123 . . .
1 "" "Mining"                             .    -.01106707 . . .
end

[Marcos Almeida]

If I understood right, you could perform the analysis, then - preserve -, then - collapse - , then - save - the collapsed dataset as a different file, then - restore - so as to keep working with the original dataset.

[Paul Halm]

Thank you for the very fast reply.

Yes I thought about that but I need to do this for 8 industries which leaves a lot of room for manual errors from my side...