Help with creating a loop to record income for all jobs a respondent reported

[Karen Arulsamy]

Hi all,

Can I please get some help in creating a loop for this code. I'll have to do the below for the available job loops (20) in the dataset. The data (ELSA) reports starting income in the UK using old money and new money. I first convert the old money to new money and then account for the time period in which the income was reported for. For e.g. some individuals report their income weekly while others yearly. The final variable to be used for analysis, in this case, is "strincome".

I've only provided data up to the 4th loop due to linesize limits. Thanks in advance for your help with this.


*Starting income - amount in new money (Loop 1)
bysort idauniq: gen rwsan_fin = .
bysort idauniq: replace rwsan_fin = rwsan if rwsan >= 0


*Starting income - amount in old money - pounds (Loop 1)
bysort idauniq: gen rwsap_fin = .
bysort idauniq: replace rwsap_fin = rwsap if rwsap >= 0

*Starting income - amount in old money - shillings (Loop 1)
bysort idauniq: gen rwsas_fin = .
bysort idauniq: replace rwsas_fin = rwsas if rwsas >= 0

*Converting to pounds
bysort idauniq: gen rwsas_finp = rwsas_fin*0.05

*Starting income - amount in old money - pence (Loop 1)
bysort idauniq: gen rwsad_fin = .
bysort idauniq: replace rwsad_fin = rwsad if rwsad >= 0

*Converting to pounds
bysort idauniq: gen rwsad_finp = rwsad_fin*0.004

*Starting income - MAIN
bysort idauniq: gen strincome = .
bysort idauniq: replace strincome = rwsap_fin + rwsas_finp + rwsad_finp
bysort idauniq: replace strincome = rwsan_fin if strincome == . & !missing(rwsan_fin)

su strincome

Variable | Obs Mean Std. Dev. Min Max
-------------+---------------------------------------------------------
strincome | 6,451 159.8318 875.7806 0 20000

*converting income to annual income:

bysort idauniq: replace strincome = strincome*52 if rws_fin == 1
bysort idauniq: replace strincome = strincome*26 if rws_fin == 2
bysort idauniq: replace strincome = strincome*17.3 if rws_fin == 3
bysort idauniq: replace strincome = strincome*13 if rws_fin == 4
bysort idauniq: replace strincome = strincome*12 if rws_fin == 5
bysort idauniq: replace strincome = strincome*6 if rws_fin == 7
bysort idauniq: replace strincome = strincome*4 if rws_fin == 13
bysort idauniq: replace strincome = strincome*2 if rws_fin == 26
bysort idauniq: replace strincome = strincome*2340 if rws_fin == 55
*assuming 9 hours a day for 5 days a week for 52 weeks of the year
bysort idauniq: replace strincome = strincome*260 if rws_fin == 66
bysort idauniq: replace strincome = strincome == . if rws_fin == 96

su strincome


----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input long idauniq byte(rws rwsm) int(rwsan rwsap rwsas) byte(rwsad rws2 rwsm2) long rwsan2 int rwsap2 byte(rwsas2 rwsad2 rws3 rwsm3) long(rwsan3 rwsap3) int(rwsas3 rwsad3) byte(rws4 rwsm4) long rwsan4 int(rwsap4 rwsas4) byte(rwsad4 rwsgn4)
100007  1 2   -1  8 10  0  1  2   -1  12 10  0  1  2   -1 15  0  0  1  2    -1 21  0  0  1
100009  1 2   -1  9  0  0  1  2   -1 250  0  0  1  2   -1 11  0  0  1  1   200 -1 -1 -1  1
100012 52 1 1200 -1 -1 -1 52  1 1200  -1 -1 -1 52  1 1800 -1 -1 -1 -1 -1    -1 -1 -1 -1 -1
100024  1 2   -1  1 10  0  1  2   -1   2  0  0  1  2   -1  1 10  0  1  2    -1  5  0  0  1
100025  1 2   -1  0 14  0  1  2   -1   8  0  0 55  1    6 -1 -1 -1 -1 -1    -1 -1 -1 -1 -1
100026  1 2   -1  1  7  8  1  2   -1  25  0  0 -1 -1   -1 -1 -1 -1 -1 -1    -1 -1 -1 -1 -1
100029  5 1   -8 -1 -1 -1 -1 -1   -1  -1 -1 -1  5  1   -8 -1 -1 -1  5 -8    -1 -1 -1 -1 -1
100039  1 2   -1  3  0  0 -1 -1   -1  -1 -1 -1  1  2   -1 17  0  0 -1 -1    -1 -1 -1 -1 -1
100044  1 2   -1  1  5  0  1  2   -1   2 10  0  1  2   -1  9 10  0 -1 -1    -1 -1 -1 -1 -1
100047  1 2   -1 -8 -8 -8  1  2   -1  -8 -8 -8  1  2   -1 -8 -8 -8  1  2    -1 -8 -8 -8 -1
100048  4 1   -8 -1 -1 -1  5  1   -8  -1 -1 -1  5  1  400 -1 -1 -1  5  1   650 -1 -1 -1  2
100050 52 1 3000 -1 -1 -1 52  1 5000  -1 -1 -1 52  1 8500 -1 -1 -1 52  1  7000 -1 -1 -1  1
100052  5 3   -1 -1 -1 -1  5  3   -1  -1 -1 -1 52  1 4000 -1 -1 -1 96  1   250 -1 -1 -1  1
100054  1 2   -1  4  4  0  1  2   -1  10  0  0  1  2   -1 16  0  0  1  1    -8 -1 -1 -1 -1
100055  1 2   -1  2  2  0  1  2   -1   4  0  0 52  1 8000 -1 -1 -1 -1 -1    -1 -1 -1 -1 -1
100056  1 2   -1  3  0  0  1  1   12  -1 -1 -1 -1 -1   -1 -1 -1 -1 -1 -1    -1 -1 -1 -1 -1
100057  1 2   -1  0 10  0 -8 -1   -1  -1 -1 -1 -8 -1   -1 -1 -1 -1 -8 -1    -1 -1 -1 -1 -1
100059  1 2   -1  1 19 11 -8 -1   -1  -1 -1 -1 -8 -1   -1 -1 -1 -1 55  1     3 -1 -1 -1  1
100063  1 2   -1  7 10  0  1  1   28  -1 -1 -1 -1 -1   -1 -1 -1 -1 -1 -1    -1 -1 -1 -1 -1
100071  1 2   -1  6  0  0 -8 -1   -1  -1 -1 -1 -8 -1   -1 -1 -1 -1 52  1 35000 -1 -1 -1  1
end

[Clyde Schechter]

Don't do this in a loop. Reshape the data to long layout, and then do it all in one fell swoop:

rename (rws rwsm rwsan rwsap rwsas rwsad) =1

reshape long rws rwsm rwsan rwsap rwsas rwsad, i(idauniq) j(_j)

*Starting income - amount in new money (Loop 1)
bysort idauniq: gen rwsan_fin = .
bysort idauniq: replace rwsan_fin = rwsan if rwsan >= 0


*Starting income - amount in old money - pounds (Loop 1)
bysort idauniq: gen rwsap_fin = .
bysort idauniq: replace rwsap_fin = rwsap if rwsap >= 0

*Starting income - amount in old money - shillings (Loop 1)
bysort idauniq: gen rwsas_fin = .
bysort idauniq: replace rwsas_fin = rwsas if rwsas >= 0

*Converting to pounds
bysort idauniq: gen rwsas_finp = rwsas_fin*0.05

*Starting income - amount in old money - pence (Loop 1)
bysort idauniq: gen rwsad_fin = .
bysort idauniq: replace rwsad_fin = rwsad if rwsad >= 0

*Converting to pounds
bysort idauniq: gen rwsad_finp = rwsad_fin*0.004

*Starting income - MAIN
bysort idauniq: gen strincome = .
bysort idauniq: replace strincome = rwsap_fin + rwsas_finp + rwsad_finp
bysort idauniq: replace strincome = rwsan_fin if strincome == . & !missing(rwsan_fin)

reshape wide rws* strincome, i(idauniq) j(_j)

Note: I have terminated your code after the commands that create the strincome variable because the subsequent lines refer to a variable, rws_fin, which does not exist in your example data and is not created by the code. But assuming that is taken care of somewhere earlier (or rws_fin is part of the data that was not shown in your example) you can put that in before the final -reshape wide- command as well.

I should point out that as far as I can see all of the -bysort idauniq:- prefixes in the code you show are unnecessary. None of the calculations there are affected in any way by grouping the data by idauniq.

[Karen Arulsamy]

Thank you very much for your help Clyde, this worked perfectly and I've also noted the comment on using bysort. Appreciate it!