Creation and multiplication of different variables in a loop

[Olivia Hamilton]

I have drawn consumer price index (cpi) variables from Excel into Stata. The variables are by quarter and year from 2002-20022 (e.g., wq12002_cpi wq22002_cpi wq32002_cpi wq42002_cpi. . . wq12022_cpi wq22022_cpi wq32022_cpi wq42022_cpi). They each have one value between 0-1 to represent the percentage quarter-year change of cpi. I need to adjust wages by cpi for each quarter. (e.g., wq12002_cpi * wq12002_wage. . . wq42022_cpi * wq42022_wage).

I created the following loop to open each file, do this calculation, and append the new variables to the main file BUT two issues 1). the new cpi variables show as missing 2). it only keeps the last variable of any set (e.g., wq42022_). This is true also for the year and quarter variables that I have created in the same loop:


************************************************** ******************************

{
preserve

capture log close

forvalues yr = 2002(1)2022 {
foreach qtr of numlist 1/4 {

global yr

global qtr

global filepath "/Users/hamilton/Dropbox/- Projects/-Quiet Quitting/DATA/Combined_Q`qtr'/"

global filename "lfs_q`qtr'y`yr'" // e.g. lfs_q2y2004

global suffix ".dta"

use "${filepath}${filename}${suffix}" ,clear


************************************************** ******************************

cd "/Users/hamilton/Dropbox/- Projects/-Quiet Quitting/DATA/"
use "/Users/hamilton/Dropbox/- Projects/-Quiet Quitting/DATA/Combined_Q`qtr'/lfs_q`qtr'y`yr'.dta"

rename *, lower

************************************************** ******************************

** TIME

// Year - Reference Week/Year

gen wQ`qtr'`yr'_yr = `yr'

label variable wQ`qtr'`yr'_yr "Q`qtr'`yr' Year (`yr')"
label values wQ`qtr'`yr'_yr wQ`qtr'`yr'_yrla

ta wQ`qtr'`yr'_yr
sum wQ`qtr'`yr'_yr


// Calendar Quarter

gen wQ`qtr'`yr'_qtr = `qtr'

label variable wQ`qtr'`yr'_qtr "Q`qtr'`yr' Quarter (`qtr)"
label values wQ`qtr'`yr'_qtr wQ`qtr'`yr'_qtrla

ta wQ`qtr'`yr'_qtr
sum wQ`qtr'`yr'_qtr


************************************************** ******************************
************************************************** ******************************

append using "/Users/hamilton/Dropbox/- Projects/-Quiet Quitting/DATA/CPI.dta"

* Wages/Pay

rename (gross99 net99 grsswk netwk hourpay) (wQ`qtr'`yr'_paygro wQ`qtr'`yr'_paynet wQ`qtr'`yr'_paygro_wk wQ`qtr'`yr'_paynet_wk wQ`qtr'`yr'_paygro_hr)

recode wQ`qtr'`yr'_paygro wQ`qtr'`yr'_paynet wQ`qtr'`yr'_paygro_wk wQ`qtr'`yr'_paynet_wk wQ`qtr'`yr'_paygro_hr (-9/-1=.)

label variable wQ`qtr'`yr'_paygro "wQ`qtr'`yr' Gross Pay (GBP) - Continuous"
label values wQ`qtr'`yr'_paygro wQ`qtr'`yr'_paygrola

label variable wQ`qtr'`yr'_paynet "wQ`qtr'`yr' Net Pay (GBP) - Continuous"
label values wQ`qtr'`yr'_paynet wQ`qtr'`yr'_paynetla

label variable wQ`qtr'`yr'_paygro_wk "wQ`qtr'`yr' Weekly Gross Pay (GBP) - Continuous"
label values wQ`qtr'`yr'_paygro_wk wQ`qtr'`yr'_paygro_wkla

label variable wQ`qtr'`yr'_paynet_wk "wQ`qtr'`yr' Weekly Net Pay (GBP) - Continuous"
label values wQ`qtr'`yr'_paynet_wk wQ`qtr'`yr'_paynet_wkla

label variable wQ`qtr'`yr'_paygro_hr "wQ`qtr'`yr' Hourly Gross Pay (GBP) - Continuous"
label values wQ`qtr'`yr'_paygro_hr wQ`qtr'`yr'_paygro_hrla


************************************************** ******************************

keep wQ`qtr'`yr'_*

save "/Users/hamilton/Dropbox/- Projects/-Quiet Quitting/DATA/Combined_Q`qtr'/lfs_q`qtr'y`yr'.dta", replace

}
}

restore
}

************************************************** ******************************
************************************************** ******************************

In doing some checks, I found that when I have the original converted Excel file and check, for example:

display wQ12002_cpi * wQ12002_cpi (i.e., cpi for quarter 1 wave 2002), the output is 1 (as expected because wQ2002 is a variable that equals 1 and it has one observation).

BUT

When I append the file to the main file and repeat the above, it gives me . (missing)


See an example of what the variables look like:


************************************************** ******************************
. ta wQ12002_cpi

wQ12002_cpi | Freq. Percent Cum.
------------+-----------------------------------
1 | 1 100.00 100.00
------------+-----------------------------------
Total | 1 100.00

. ta wQ12003_cpi

wQ12003_cpi | Freq. Percent Cum.
------------+-----------------------------------
.9853799 | 1 100.00 100.00
------------+-----------------------------------
Total | 1 100.00

************************************************** ******************************

-> ta of wQ42003_yr

Q42003 Year |
(2003) | Freq. Percent Cum.
------------+-----------------------------------
2003 | 127,021 100.00 100.00
------------+-----------------------------------
Total | 127,021 100.00


-> ta of wQ42003_qtr

Q42003 |
Quarter |
(`qtr) | Freq. Percent Cum.
------------+-----------------------------------
4 | 127,021 100.00 100.00
------------+-----------------------------------
Total | 127,021 100.00

************************************************** ******************************

. sum wQ42003_wage wQ42003_wage_cpi wQ42003_wage_cpi_categorical

Variable | Obs Mean Std. dev. Min Max
-------------+---------------------------------------------------------
wQ42003_wage | 13,814 9175.29 14361.97 0 99995
wQ42003_wa~pi | 0
wQ42003_wa~al | 0

************************************************** ******************************


Anyone have any insights?

Thanks in advance!

[William Lisowski]

Apparent duplicate of an earlier post at

https://www.statalist.org/forums/for...bles-in-a-loop