how to create a new variable that is the subtraction of a column

MH Sandoval

Join Date: Aug 2017
Posts: 23

how to create a new variable that is the subtraction of a column

23 Sep 2017, 14:19

Colleagues
I have a database for each addresses, about the time uses of all people over 10 years of age (time_pro_individual). And, I want to be able to perform a subtraction of the time spent by the woman (head of household or spouse) less the time spent by the man (or her husband) within the same address.

so far I have only been able to estimate the total amount of time spent on an activity by men and women (ie, a wife and her husband, or vice versa) for each household (variable time_product_housing)

Description of variables:

Directory: is the identifier of each dwelling.
sequence: is the identifier of each domicile within the same dwelling
codigodom: is the union of directory + sequence (this is the variable that I have been using)
p6020 refers to sex: 1 man and 2 woman
p425 refers to relationship of parentage: 1 is head of household and 2 is spouse
time_pro_individual: is the time spent in an activity "x" (in minutes)
time_product_housing is the total time spent on activity "x" by the head of household and his / her relationship.

Dataex

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

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(DIRECTORIO SECUENCIA_P) long codigodom double(p6020 p425) float(time_pro_individual time_product_housing)
2915591 1 29155911 2  1   0   0
2915592 1 29155921 2  1  90  90
2915593 1 29155931 2  1  30  30
2915593 1 29155931 2  3   0   .
2915593 1 29155931 2  3   0   .
2915594 1 29155941 1  1   0 180
2915594 1 29155941 2  2 180 180
2915594 1 29155941 1  3   0   .
2915594 1 29155941 1  3   0   .
2915595 1 29155951 1  1  30  30
2915595 1 29155951 2  2   0  30
2915595 1 29155951 1  3   0   .
2915596 1 29155961 1  1 210 300
2915596 1 29155961 2  2  90 300
2915596 1 29155961 1  3   0   .
2915597 1 29155971 1  1   0 240
2915597 1 29155971 2  2 240 240
2915597 1 29155971 2  3 300   .
2915597 1 29155971 2  4   0   .
2915598 1 29155981 2  1  60  60
2915598 1 29155981 1  3   0   .
2915599 1 29155991 1  1   0 120
2915599 1 29155991 2  2 120 120
2915600 1 29156001 1  1   0   0
2915600 1 29156001 2  5 210   .
2915600 1 29156001 1  8  30   .
2915601 1 29156011 1  1   0 120
2915601 1 29156011 2  2 120 120
2915601 1 29156011 1  3  60   .
2915601 1 29156011 2  3   0   .
2915602 1 29156021 1  1   0 390
2915602 1 29156021 2  2 390 390
2915602 1 29156021 1  3   0   .
2915602 1 29156021 1  3   0   .
2915602 1 29156021 1  5   0   .
2915602 1 29156021 1  6  10   .
2915603 1 29156031 2  1  35  35
2915603 1 29156031 1  3   0   .
2915604 1 29156041 2  1  70  70
2915604 2 29156042 1  1   0   0
2915604 2 29156042 1  6   0   .
2915604 2 29156042 1  6   0   .
2915605 1 29156051 2  1 135 135
2915605 1 29156051 1  3   0   .
2915605 1 29156051 1  3   0   .
2915606 1 29156061 1  1   0  10
2915606 1 29156061 2  2  10  10
2915606 1 29156061 1  3   0   .
2915607 1 29156071 1  1  60 135
2915607 1 29156071 2  2  75 135
2915608 1 29156081 1  1   0 150
2915608 1 29156081 2  2 150 150
2915608 1 29156081 2  3   0   .
2915608 1 29156081 1  4   0   .
2915609 1 29156091 1  1   0 150
2915609 1 29156091 2  2 150 150
2915609 1 29156091 2  3   0   .
2915610 1 29156101 2  1 155 195
2915610 1 29156101 1  2  40 195
2915610 1 29156101 2  4 170   .
2915611 1 29156111 2  1  25  25
2915611 1 29156111 1  3  40   .
2915611 1 29156111 1  3   0   .
2915611 1 29156111 2  5  40   .
2915611 1 29156111 2  8 140   .
2915612 1 29156121 1  1   0 120
2915612 1 29156121 2  2 120 120
2915612 1 29156121 2  3  90   .
2915612 1 29156121 2  3  90   .
2915614 1 29156141 1  1   2  62
2915614 1 29156141 2  2  60  62
2915614 1 29156141 1  3   0   .
2915614 1 29156141 1  3   0   .
2915615 1 29156151 2  1 240 240
2915615 1 29156151 1  3   0   .
2915615 1 29156151 2  3   0   .
2915615 1 29156151 2  3 250   .
2915615 1 29156151 1  4   0   .
2915616 1 29156161 1  1  30  30
2915616 1 29156161 2  2   0  30
2915616 1 29156161 1  3   5   .
2915618 1 29156181 1  1   0   0
2915618 1 29156181 1  3   0   .
2915618 1 29156181 2  3   0   .
2915618 1 29156181 2  3  10   .
2915618 1 29156181 1  4   0   .
2915619 1 29156191 2  1   0   0
2915619 1 29156191 1  3   0   .
2915619 1 29156191 2  3 120   .
2915619 1 29156191 1  4   0   .
2915620 1 29156201 1  1   2   2
2915620 1 29156201 2  5 220   .
2915621 1 29156211 1  1   0   0
2915621 1 29156211 1 10   0   .
2915622 1 29156221 2  1   0   0
2915622 1 29156221 1  2   0   0
2915622 1 29156221 2  3   0   .
2915622 1 29156221 1  3   0   .
2915622 1 29156221 2  5   0   .
2915623 1 29156231 1  1  80  80
end
label values p6020 p6020
label def p6020 1 "hombre", modify
label def p6020 2 "mujer", modify
label values p425 p425
label def p425 1 "jefe(a) del hogar", modify
label def p425 2 "esposo(a) o compañero(a)", modify
label def p425 3 "hijo(a), hijastro(a)", modify
label def p425 4 "nieto(a)", modify
label def p425 5 "padre, madre, suegro(a)", modify
label def p425 6 "hermano(a), hermanastro(a)", modify
label def p425 8 "otro pariente del jefe(a)", modify
label def p425 10 "otro no pariente", modify

------------------ copy up to and including the previous line ------------------

in turn, would like to be able to divide the sum of the total time spent by all women over the total time spent in that activity by all the individuals at the base (men and women)

I appreciate your help

Moses

Tags: None

Andrew Musau

Join Date: Oct 2014
Posts: 9117

24 Sep 2017, 14:31

Thanks for the data example.

I have a database for each addresses, about the time uses of all people over 10 years of age (time_pro_individual). And, I want to be able to perform a subtraction of the time spent by the woman (head of household or spouse) less the time spent by the man (or her husband) within the same address.

I assume that the variable identifying a household is "codigodom". Note that this difference is only applicable to a household that contains a husband and spouse. Therefore, I would approach it as follows:

Code:

*PRESERVE EXISTING DATASET
preserve

*KEEP ONLY OBSERVATIONS RELATING TO HUSBAND AND SPOUSE
keep if p425< 3


*IDENTIFY ANOMALOUS HOUSEHOLDS (CHECK THAT RETAINED HOUSEHOLDS HAVE BOTH HUSBAND AND WIFE)
sort codigodom p6020
by codigodom: egen total= total( p6020 )
list codigodom if total !=3

*IF ALL IS OK, PROCEED...
drop total
bys codigodom: gen wanted = time_pro_individual[2]- time_pro_individual[1]

*KEEP 1 OBS. PER ADDRESS
bys codigodom: keep if _n==1
tempfile wanted
save `wanted'

*RESTORE AND MERGE
restore
merge m:1 codigodom using `wanted'
drop _merge

in turn, would like to be able to divide the sum of the total time spent by all women over the total time spent in that activity by all the individuals at the base (men and women)

Code:

*TOTAL TIME SPENT BY WOMEN PER ADDRESS
bys codigodom: egen countw= total( time_pro_individual ) if p6020==2
bys codigodom: egen totalw= max( countw )

*TOTAL TIME SPENT PER ADDRESS
bys codigodom: egen count= total( time_pro_individual )
bys codigodom: egen total= max( count)

*GET RATIO
drop count countw
gen wanted2= totalw/ total
l in 1/19, sepby( codigodom )

Code:

. l in 1/19, sepby( codigodom )

     +-------------------------------------------------------------------------------------------------------------------------------+
     | DIRECT~O   SECUEN~P   codigo~m    p6020                       p425   time_p~l   time_p~g   wanted   totalw   total   wanted2 |
     |-------------------------------------------------------------------------------------------------------------------------------|
  1. |  2915591          1   29155911    mujer          jefe(a) del hogar          0          0        .         0       0         . |
     |-------------------------------------------------------------------------------------------------------------------------------|
  2. |  2915592          1   29155921    mujer          jefe(a) del hogar         90         90        .        90      90         1 |
     |-------------------------------------------------------------------------------------------------------------------------------|
  3. |  2915593          1   29155931    mujer          jefe(a) del hogar         30         30        .        30      30         1 |
  4. |  2915593          1   29155931    mujer       hijo(a), hijastro(a)          0          .        .        30      30         1 |
  5. |  2915593          1   29155931    mujer       hijo(a), hijastro(a)          0          .        .        30      30         1 |
     |-------------------------------------------------------------------------------------------------------------------------------|
  6. |  2915594          1   29155941   hombre          jefe(a) del hogar          0        180      180       180     180         1 |
  7. |  2915594          1   29155941    mujer   esposo(a) o compañero(a)        180        180      180       180     180         1 |
  8. |  2915594          1   29155941   hombre       hijo(a), hijastro(a)          0          .      180       180     180         1 |
  9. |  2915594          1   29155941   hombre       hijo(a), hijastro(a)          0          .      180       180     180         1 |
     |-------------------------------------------------------------------------------------------------------------------------------|
 10. |  2915595          1   29155951   hombre          jefe(a) del hogar         30         30      -30         0      30         0 |
 11. |  2915595          1   29155951    mujer   esposo(a) o compañero(a)          0         30      -30         0      30         0 |
 12. |  2915595          1   29155951   hombre       hijo(a), hijastro(a)          0          .      -30         0      30         0 |
     |-------------------------------------------------------------------------------------------------------------------------------|
 13. |  2915596          1   29155961   hombre          jefe(a) del hogar        210        300     -120        90     300        .3 |
 14. |  2915596          1   29155961    mujer   esposo(a) o compañero(a)         90        300     -120        90     300        .3 |
 15. |  2915596          1   29155961   hombre       hijo(a), hijastro(a)          0          .     -120        90     300        .3 |
     |-------------------------------------------------------------------------------------------------------------------------------|
 16. |  2915597          1   29155971   hombre          jefe(a) del hogar          0        240      240       540     540         1 |
 17. |  2915597          1   29155971    mujer   esposo(a) o compañero(a)        240        240      240       540     540         1 |
 18. |  2915597          1   29155971    mujer       hijo(a), hijastro(a)        300          .      240       540     540         1 |
 19. |  2915597          1   29155971    mujer                   nieto(a)          0          .      240       540     540         1 |
     +-------------------------------------------------------------------------------------------------------------------------------+

Last edited by Andrew Musau; 24 Sep 2017, 14:51.

Comment

MH Sandoval

Join Date: Aug 2017

Posts: 23
#3

24 Sep 2017, 16:54

Hello Andrew.

Thank you very much for your help. I was doing something very similar to how you indicate me. But with your example the process will be more "clean".

Thank you very much

Regards

Moses
Comment

Announcement

how to create a new variable that is the subtraction of a column

Comment

Comment