Creating new variable (subtracting) same individual

[Kylie Borg]

Hi,

I am using individual-level dataset across 4 years to try and calculate the mobility out of low wage between 2020 and 2017. I created these dummy variables (delta2020 and delta2017) expressing 1 if low wage and 0 if not.
However, I need to subtract delta2020 with delta2017 for the same individual (Person_Ref1).

How can I create a variable like diff below?


input Person_Ref1Person_Ref2 delta2020 delta2017 diff
"1000501" "2017" . 1 .
"1000501" "2018" . . .
"1000501" "2019" . . .
"1000501" "2020" 1 . 0
"1000502" "2017" . 0 .
"1000502" "2018" . . .
"1000502" "2019" . . .
"1000502" "2020" 0 . 0
"1000503" "2017" . 0 .
"1000503" "2018" . . .
"1000503" "2019" . . .
"1000503" "2020" 1 . 1
"1000701" "2017" . 1 .
"1000701" "2018" . . .
"1000701" "2019" . . .
"1000701" "2020" 1 . 0
"1000702" "2017" . 1 .
"1000702" "2018" . . .
"1000702" "2019" . . .
"1000702" "2020" 1 . 0
"1000703" "2017" . 0 .
"1000703" "2018" . . .
"1000703" "2019" . . .
"1000703" "2020" 0 . 0
"1002601" "2017" . 0 .
"1002601" "2018" . . .
"1002601" "2019" . . .
"1002601" "2020" 0 . 0
"1004203" "2017" . 1 .
"1004203" "2018" . . .
"1004203" "2019" . . .
"1004203" "2020" 1 . 1


Appreciate your help and thanks in advance

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 Person_Ref1 int Person_Ref2 float(delta2020 delta2017 diff)
"1000501" 2017 . 1 .
"1000501" 2018 . . .
"1000501" 2019 . . .
"1000501" 2020 1 . 0
"1000502" 2017 . 0 .
"1000502" 2018 . . .
"1000502" 2019 . . .
"1000502" 2020 0 . 0
"1000503" 2017 . 0 .
"1000503" 2018 . . .
"1000503" 2019 . . .
"1000503" 2020 1 . 1
"1000701" 2017 . 1 .
"1000701" 2018 . . .
"1000701" 2019 . . .
"1000701" 2020 1 . 0
"1000702" 2017 . 1 .
"1000702" 2018 . . .
"1000702" 2019 . . .
"1000702" 2020 1 . 0
"1000703" 2017 . 0 .
"1000703" 2018 . . .
"1000703" 2019 . . .
"1000703" 2020 0 . 0
"1002601" 2017 . 0 .
"1002601" 2018 . . .
"1002601" 2019 . . .
"1002601" 2020 0 . 0
"1004203" 2017 . 1 .
"1004203" 2018 . . .
"1004203" 2019 . . .
"1004203" 2020 1 . 1
end

bys Person_Ref1 (Person_Ref2): assert Person_Ref2[1]==2017 & Person_Ref2[_N]==2020
bys Person_Ref1 (Person_Ref2): g wanted= delta2020[_N] - delta2017[1]

Res.:

. l, sepby(Person_Ref1)

     +-----------------------------------------------------------+
     | Person~1   Person~2   del~2020   del~2017   diff   wanted |
     |-----------------------------------------------------------|
  1. |  1000501       2017          .          1      .        0 |
  2. |  1000501       2018          .          .      .        0 |
  3. |  1000501       2019          .          .      .        0 |
  4. |  1000501       2020          1          .      0        0 |
     |-----------------------------------------------------------|
  5. |  1000502       2017          .          0      .        0 |
  6. |  1000502       2018          .          .      .        0 |
  7. |  1000502       2019          .          .      .        0 |
  8. |  1000502       2020          0          .      0        0 |
     |-----------------------------------------------------------|
  9. |  1000503       2017          .          0      .        1 |
 10. |  1000503       2018          .          .      .        1 |
 11. |  1000503       2019          .          .      .        1 |
 12. |  1000503       2020          1          .      1        1 |
     |-----------------------------------------------------------|
 13. |  1000701       2017          .          1      .        0 |
 14. |  1000701       2018          .          .      .        0 |
 15. |  1000701       2019          .          .      .        0 |
 16. |  1000701       2020          1          .      0        0 |
     |-----------------------------------------------------------|
 17. |  1000702       2017          .          1      .        0 |
 18. |  1000702       2018          .          .      .        0 |
 19. |  1000702       2019          .          .      .        0 |
 20. |  1000702       2020          1          .      0        0 |
     |-----------------------------------------------------------|
 21. |  1000703       2017          .          0      .        0 |
 22. |  1000703       2018          .          .      .        0 |
 23. |  1000703       2019          .          .      .        0 |
 24. |  1000703       2020          0          .      0        0 |
     |-----------------------------------------------------------|
 25. |  1002601       2017          .          0      .        0 |
 26. |  1002601       2018          .          .      .        0 |
 27. |  1002601       2019          .          .      .        0 |
 28. |  1002601       2020          0          .      0        0 |
     |-----------------------------------------------------------|
 29. |  1004203       2017          .          1      .        0 |
 30. |  1004203       2018          .          .      .        0 |
 31. |  1004203       2019          .          .      .        0 |
 32. |  1004203       2020          1          .      1        0 |
     +-----------------------------------------------------------+

.

[Kylie Borg]

I don't think that you understood what I need exactly...

I want to create a variable like diff as the one above because that is done manually. The variable should be the change of delta 2020 and 2017 like diff there, for the same individual according to Person_Ref1

[Andrew Musau]

The only difference that I can see is that the values of your wanted variable are missing prior to 2020 and you miscalculate the last difference. So append to the code:

replace wanted= . if Person_Ref2!=2020

Otherwise sorry, I cannot follow what is needed.

[Kylie Borg]

Apologies maybe I am not making myself clear.

I want to create variable "diff" that is the change of delta2020 and delta2017. I would like a code to be able to subtract column delta2020 and 2017, however, according to the same individual.
For example, the first one would be 1 (from 4th row of delta2020) - 1 (from 1st row of delta2017) = 0

[Andrew Musau]

In what way does the code in #2 with the code in #4 appended not do that? For example, wanted=0 for individual with Person_Ref1=1000501.

[Kylie Borg]

Apologies I got confused when I saw the other values for 2017,2018 and 2019.
I used code: replace wanted = . if missing( delta2020 )

and managed!

[CODE]
* Example generated by -dataex-. For more info, type help dataex
clear
input str7 Person_Ref1 str4 Person_Ref2 float(delta2020 delta2017 wanted)
"1000501" "2017" . 1 .
"1000501" "2018" . . .
"1000501" "2019" . . .
"1000501" "2020" 1 . 0
"1000502" "2017" . 0 .
"1000502" "2018" . . .
"1000502" "2019" . . .
"1000502" "2020" 0 . 0
"1000503" "2017" . 0 .
"1000503" "2018" . . .
"1000503" "2019" . . .
"1000503" "2020" 1 . 1
"1000701" "2017" . 1 .
"1000701" "2018" . . .
"1000701" "2019" . . .
"1000701" "2020" 1 . 0
"1000702" "2017" . 1 .
"1000702" "2018" . . .
"1000702" "2019" . . .
"1000702" "2020" 1 . 0
"1000703" "2017" . 0 .
"1000703" "2018" . . .
"1000703" "2019" . . .
"1000703" "2020" 0 . 0
"1002601" "2017" . 0 .
"1002601" "2018" . . .
"1002601" "2019" . . .
"1002601" "2020" 0 . 0
"1004203" "2017" . 1 .
"1004203" "2018" . . .
"1004203" "2019" . . .
"1004203" "2020" 1 . 0
"1005301" "2017" . 1 .
"1005301" "2018" . . .
"1005301" "2019" . . .
"1005301" "2020" 1 . 0
"1006101" "2017" . 0 .
"1006101" "2018" . . .
"1006101" "2019" . . .
"1006101" "2020" 1 . 1
"1006102" "2017" . 0 .
"1006102" "2018" . . .
"1006102" "2019" . . .
"1006102" "2020" 0 . 0
"1006201" "2017" . 1 .
"1006201" "2018" . . .
"1006201" "2019" . . .
"1006201" "2020" 1 . 0
"1006203" "2017" . 0 .
"1006203" "2018" . . .
"1006203" "2019" . . .
"1006203" "2020" 0 . 0
"1008802" "2017" . 0 .
"1008802" "2018" . . .
"1008802" "2019" . . .
"1008802" "2020" 0 . 0
"1009503" "2017" . 1 .
"1009503" "2018" . . .
"1009503" "2019" . . .
"1009503" "2020" 1 . 0
"1010002" "2017" . 1 .
"1010002" "2018" . . .
"1010002" "2019" . . .
"1010002" "2020" 1 . 0
"1010901" "2017" . 1 .
"1010901" "2018" . . .
"1010901" "2019" . . .
"1010901" "2020" 1 . 0
"1011302" "2017" . 0 .
"1011302" "2018" . . .
"1011302" "2019" . . .
"1011302" "2020" 0 . 0
"1012201" "2017" . 1 .
"1012201" "2018" . . .
"1012201" "2019" . . .
"1012201" "2020" 1 . 0
"1012202" "2017" . 1 .
"1012202" "2018" . . .
"1012202" "2019" . . .
"1012202" "2020" 1 . 0
"1012801" "2017" . 1 .
"1012801" "2018" . . .
"1012801" "2019" . . .
"1012801" "2020" 1 . 0
"1013602" "2017" . 1 .
"1013602" "2018" . . .
"1013602" "2019" . . .
"1013602" "2020" 1 . 0
"1013603" "2017" . 1 .
"1013603" "2018" . . .
"1013603" "2019" . . .
"1013603" "2020" 1 . 0
"1013604" "2017" . 0 .
"1013604" "2018" . . .
"1013604" "2019" . . .
"1013604" "2020" 1 . 1
"1014903" "2017" . 0 .
"1014903" "2018" . . .
"1014903" "2019" . . .
"1014903" "2020" 1 . 1
end


Thanks a lot for your help!