Hello Everyone,
I just wanted to create another variable by using mergeid (which is the first column in the data example) by using the last two digits for which 1 means respondent and 2 means spouse. Actually, I want to transform the data to wide format wherein one row i will have variables on respondent and spouse. Hence i understand in order to do this i need to create on more column where 1 means respondent and 2 means spouse which will enable me to use the wide command for this dataset. Any suggestion how can i pick up last two digits and further transformation?
I just wanted to create another variable by using mergeid (which is the first column in the data example) by using the last two digits for which 1 means respondent and 2 means spouse. Actually, I want to transform the data to wide format wherein one row i will have variables on respondent and spouse. Hence i understand in order to do this i need to create on more column where 1 means respondent and 2 means spouse which will enable me to use the wide command for this dataset. Any suggestion how can i pick up last two digits and further transformation?
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str12 mergeid str11 hhid1 str12 mergeidp1 str15 coupleid1 byte(country language hhsize htype) double(sic tax ytotg ytotn ynrpg ynrpn hhyotg) "AT-000327-01" "AT-000327-A" "AT-000327-02" "AT-000327-01-02" 11 11 2 3 614.52001953125 1683.119873046875 15055.3203125 12757.6796875 0 0 0 "AT-000327-02" "AT-000327-A" "AT-000327-01" "AT-000327-01-02" 11 11 2 3 570.7200317382813 1413.47998046875 13023.958984375 11039.759765625 0 0 0 "AT-001816-01" "AT-001816-A" "AT-001816-02" "AT-001816-01-02" 11 11 2 3 914.6400146484375 0 8199.92578125 7285.2861328125 0 0 18168.208984375 "AT-001816-02" "AT-001816-A" "AT-001816-01" "AT-001816-01-02" 11 11 2 3 799.4400634765625 0 1800 1000.5599365234375 0 0 18168.208984375 "AT-002132-01" "AT-002132-A" "" "" 11 11 6 1 1072.199951171875 1064.6400146484375 10714.4052734375 8577.5654296875 0 0 9906.8818359375 "AT-002355-01" "AT-002355-A" "AT-002355-02" "AT-002355-01-02" 11 11 2 3 630 2384.159912109375 14412.240234375 11398.080078125 0 0 590 "AT-002355-02" "AT-002355-A" "AT-002355-01" "AT-002355-01-02" 11 11 2 3 309.7200012207031 263.0400085449219 7092.2060546875 6519.4453125 0 0 590 "AT-004234-01" "AT-004234-A" "AT-004234-02" "AT-004234-01-02" 11 11 2 2 0 1428.1199951171875 12087 10658.8798828125 32479.44140625 20871.58203125 800 "AT-004379-01" "AT-004379-A" "" "" 11 11 1 1 420 954.3599853515625 9599.9658203125 8225.60546875 0 0 0 "AT-004855-01" "AT-004855-A" "AT-004855-02" "AT-004855-01-02" 11 11 2 3 577.4400024414063 2038.320068359375 13229.96484375 10614.205078125 0 0 0 "AT-004855-02" "AT-004855-A" "AT-004855-01" "AT-004855-01-02" 11 11 2 3 0 0 0 0 0 0 0 "AT-004974-01" "AT-004974-A" "" "" 11 11 1 1 1545.719970703125 10186.6796875 37339.78515625 25607.384765625 0 0 624.54736328125 "AT-005460-01" "AT-005460-A" "AT-005460-02" "AT-005460-01-02" 11 11 2 2 1491.1199951171875 3951.840087890625 20426.005859375 14983.044921875 52436.28125 33580.90234375 19600 "AT-007760-01" "AT-007760-A" "" "" 11 11 1 1 446.27996826171875 1179.60009765625 10284.513671875 8658.634765625 0 0 0 "AT-008702-01" "AT-008702-A" "AT-008702-02" "AT-008702-01-02" 11 11 2 2 4667.76025390625 7561.19970703125 38383.203125 26154.240234375 54893.40234375 34811.89453125 0 "AT-008866-01" "AT-008866-A" "" "" 11 11 1 1 1050 6407.5205078125 25014.205078125 17556.685546875 0 0 0 "AT-009524-01" "AT-009524-A" "AT-009524-02" "AT-009524-01-02" 11 11 2 2 787.5599365234375 3765 18000 13447.439453125 33084 22870.8359375 0 "AT-010768-01" "AT-010768-A" "" "" 11 11 1 1 1519.9200439453125 5577.7197265625 25417.166015625 18319.52734375 0 0 0 "AT-010904-01" "AT-010904-A" "AT-010904-02" "AT-010904-01-02" 11 11 2 3 1155 6716.759765625 26457.15234375 18585.392578125 0 0 0 "AT-010904-02" "AT-010904-A" "AT-010904-01" "AT-010904-01-02" 11 11 2 3 892.4400634765625 4587.9599609375 20457.0859375 14976.685546875 0 0 0 "AT-010955-01" "AT-010955-A" "AT-010955-02" "AT-010955-01-02" 11 11 2 3 787.5599365234375 3780.719970703125 18028.080078125 13459.80078125 0 0 16000 "AT-010955-02" "AT-010955-A" "AT-010955-01" "AT-010955-01-02" 11 11 2 3 525 1685.159912109375 12834.634765625 10624.474609375 0 0 16000 "AT-011129-01" "AT-011129-A" "AT-011129-02" "AT-011129-01-02" 11 11 2 2 2030.0400390625 7816.919921875 30031.353515625 20184.392578125 8227.439453125 6458.0595703125 0 "AT-011464-01" "AT-011464-A" "" "" 11 11 1 1 7306.31982421875 3621.599853515625 25050 14122.0810546875 0 0 0 "AT-012207-01" "AT-012207-A" "AT-012207-02" "AT-012207-01-02" 11 11 2 3 1030.0799560546875 5584.2001953125 23582.43359375 16968.154296875 0 0 0 "AT-012207-02" "AT-012207-A" "AT-012207-01" "AT-012207-01-02" 11 11 2 3 213.1199951171875 0 4872.0341796875 4658.9140625 0 0 0 "AT-013017-01" "AT-013017-A" "" "" 11 11 1 1 1416.239990234375 4764.9599609375 21299.7421875 15118.54296875 0 0 0 "AT-013179-01" "AT-013179-A" "" "" 11 11 2 5 577.4400024414063 2046.840087890625 13244.96484375 10620.6845703125 0 0 0 "AT-013179-02" "AT-013179-A" "" "" 11 11 2 5 315 281.4000244140625 7200 6603.599609375 0 0 0 "AT-013454-01" "AT-013454-A" "AT-013454-02" "AT-013454-01-02" 11 11 2 2 0 6.239999771118164 375 368.760009765625 86389.4375 53630.1015625 30000 "AT-014640-01" "AT-014640-A" "" "" 11 11 1 1 516 1647.9599609375 12855.92578125 10691.9658203125 0 0 96 "AT-015615-01" "AT-015615-A" "" "" 11 11 1 1 2365.7998046875 11456.0400390625 44145.73828125 30323.8984375 0 0 0 "AT-016234-01" "AT-016234-A" "" "" 11 11 1 1 4725 44467.796875 108000 58807.203125 0 0 0 "AT-016245-01" "AT-016245-A" "" "" 11 11 1 1 967.5599365234375 7548.599609375 28319.314453125 19803.154296875 0 0 0 "AT-016392-01" "AT-016392-A" "AT-016392-02" "AT-016392-01-02" 11 11 2 3 183.72000122070312 104.52000427246094 4449.92529296875 4161.685546875 0 0 300 "AT-016392-02" "AT-016392-A" "AT-016392-01" "AT-016392-01-02" 11 11 2 3 1461.60009765625 4891.56005859375 21849.958984375 15496.80078125 0 0 300 "AT-016537-01" "AT-016537-A" "AT-016537-02" "AT-016537-01-02" 11 11 3 3 5578.7998046875 3329.76025390625 26050.078125 17141.51953125 0 0 42000 "AT-016537-02" "AT-016537-A" "AT-016537-01" "AT-016537-01-02" 11 11 3 3 6437.16015625 4210.56005859375 30200.0390625 19552.3203125 0 0 42000 "AT-017298-01" "AT-017298-A" "AT-017298-02" "AT-017298-01-02" 11 11 2 2 0 187.55999755859375 750 562.4400024414063 44373.2421875 27148.7734375 119933.296875 "AT-018404-01" "AT-018404-A" "AT-018404-02" "AT-018404-01-02" 11 11 2 3 395.6400146484375 805.800048828125 9072.068359375 7870.62890625 0 0 16000 "AT-018404-02" "AT-018404-A" "AT-018404-01" "AT-018404-01-02" 11 11 2 3 630 2377.320068359375 14400 11392.6796875 0 0 16000 "AT-018645-01" "AT-018645-A" "" "" 11 11 1 1 425.03997802734375 988.9200439453125 9716.400390625 8302.439453125 0 0 0 "AT-018838-01" "AT-018838-A" "AT-018838-02" "AT-018838-01-02" 11 11 2 3 1623.9599609375 16392.720703125 40499.96484375 22483.28515625 0 0 5000 "AT-018838-02" "AT-018838-A" "AT-018838-01" "AT-018838-01-02" 11 11 2 3 1623.719970703125 8395.19921875 21500.041015625 11481.1201171875 0 0 5000 "AT-019273-01" "AT-019273-A" "AT-019273-02" "AT-019273-01-02" 11 11 2 3 577.4400024414063 2021.52001953125 13199.96484375 10601.0048828125 0 0 12000 "AT-019273-02" "AT-019273-A" "AT-019273-01" "AT-019273-01-02" 11 11 2 3 630 2377.320068359375 14400 11392.6796875 0 0 12000 "AT-019860-01" "AT-019860-A" "AT-019860-02" "AT-019860-01-02" 11 11 3 3 652.199951171875 161.63999938964844 2446.7998046875 1632.9599609375 0 0 600 "AT-019860-02" "AT-019860-A" "AT-019860-01" "AT-019860-01-02" 11 11 3 3 929.3999633789063 161.63999938964844 1246.800048828125 155.7600555419922 0 0 600 "AT-020377-01" "AT-020377-A" "AT-020377-02" "AT-020377-01-02" 11 11 5 3 787.5599365234375 3960.35986328125 18590.041015625 13842.1201171875 0 0 0 "AT-020377-02" "AT-020377-A" "AT-020377-01" "AT-020377-01-02" 11 11 5 3 0 0 0 0 0 0 0 "AT-020412-01" "AT-020412-A" "AT-020412-02" "AT-020412-01-02" 11 11 2 3 652.199951171875 8.519999504089356 2134.080078125 1473.3599853515625 0 0 6000 "AT-020412-02" "AT-020412-A" "AT-020412-01" "AT-020412-01-02" 11 11 2 3 652.199951171875 8.519999504089356 374.0400085449219 0 0 0 6000 "AT-020465-01" "AT-020465-A" "AT-020465-02" "AT-020465-01-02" 11 11 2 3 2258.52001953125 1408.199951171875 15000 11333.2802734375 0 0 1000 "AT-020465-02" "AT-020465-A" "AT-020465-01" "AT-020465-01-02" 11 11 2 3 682.5599975585938 2918.760009765625 15600.03515625 11998.71484375 0 0 1000 "AT-020895-01" "AT-020895-A" "" "" 11 11 3 1 11323.919921875 100762.5625 230098.8125 118012.328125 0 0 1300 "AT-020975-01" "AT-020975-A" "AT-020975-02" "AT-020975-01-02" 11 11 2 3 974.3999633789063 2270.52001953125 14400 11155.080078125 0 0 1190.298828125 "AT-020975-02" "AT-020975-A" "AT-020975-01" "AT-020975-01-02" 11 11 2 3 2227.2001953125 10830.599609375 39000 25942.201171875 0 0 1190.298828125 "AT-022848-01" "AT-022848-A" "" "" 11 11 1 1 262.44000244140625 40.439998626708984 5999.9658203125 5697.0859375 0 0 0 "AT-023465-01" "AT-023465-A" "" "" 11 11 1 1 2574.840087890625 532.3200073242188 12734.3994140625 9627.240234375 0 0 0 "AT-024225-01" "AT-024225-A" "AT-024225-02" "AT-024225-01-02" 11 11 2 3 1623.719970703125 0 360 0 0 0 0 "AT-024225-02" "AT-024225-A" "AT-024225-01" "AT-024225-01-02" 11 11 2 3 2799.119873046875 357.239990234375 13395.48046875 10239.1201171875 0 0 0 "AT-024620-01" "AT-024620-A" "" "" 11 11 1 1 525 1777.7999267578125 12200.07421875 9897.2744140625 0 0 0 "AT-024925-01" "AT-024925-A" "" "" 11 11 1 1 782.1600341796875 0 6240 5457.83984375 0 0 0 "AT-025115-01" "AT-025115-A" "AT-025115-02" "AT-025115-01-02" 11 11 2 3 314.5199890136719 350.8800048828125 7187.9658203125 6522.5654296875 0 0 320 "AT-025115-02" "AT-025115-A" "AT-025115-01" "AT-025115-01-02" 11 11 2 3 0 0 3840 3840 0 0 320 "AT-025847-01" "AT-025847-A" "" "" 11 11 1 1 0 712.5599975585938 8400.0341796875 7687.474609375 0 0 0 "AT-026066-01" "AT-026066-A" "" "" 11 11 1 1 495 1578.47998046875 13204.80078125 11131.3203125 0 0 0 "AT-026212-01" "AT-026212-A" "AT-026212-02" "AT-026212-01-02" 11 11 2 2 257.2799987792969 16.31999969482422 5880.0341796875 5606.43408203125 9073.560546875 8278.2119140625 0 "AT-027032-01" "AT-027032-A" "AT-027032-02" "AT-027032-01-02" 11 11 2 3 537.47998046875 1753.7999267578125 12291.8232421875 10000.54296875 0 0 1100.1798095703125 "AT-027032-02" "AT-027032-A" "AT-027032-01" "AT-027032-01-02" 11 11 2 3 1205.8800048828125 7134.83984375 27562.62890625 19221.908203125 0 0 1100.1798095703125 "AT-027287-01" "AT-027287-A" "" "" 11 11 1 1 367.55999755859375 609.8399658203125 8420.07421875 7442.673828125 0 0 0 "AT-027751-01" "AT-027751-A" "AT-027751-02" "AT-027751-01-02" 11 11 2 3 1623.719970703125 0 6000 4376.2802734375 0 0 0 "AT-027751-02" "AT-027751-A" "AT-027751-01" "AT-027751-01-02" 11 11 2 3 0 0 0 0 0 0 0 "AT-027901-01" "AT-027901-A" "AT-027901-02" "AT-027901-01-02" 11 11 2 3 945 5691.23974609375 22954.6796875 16318.439453125 0 0 300 "AT-027901-02" "AT-027901-A" "AT-027901-01" "AT-027901-01-02" 11 11 2 3 157.55999755859375 468 5471.759765625 4846.2001953125 0 0 300 "AT-028625-01" "AT-028625-A" "" "" 11 11 1 1 0 1832.159912109375 12006.39453125 10174.234375 0 0 0 "AT-028724-01" "AT-028724-A" "AT-028724-02" "AT-028724-01-02" 11 11 2 3 243.59999084472656 12.479999542236328 3650.0400390625 3393.9599609375 0 0 0 "AT-028724-02" "AT-028724-A" "AT-028724-01" "AT-028724-01-02" 11 11 2 3 1623.719970703125 12.479999542236328 1850.0400390625 213.8400421142578 0 0 0 "AT-028778-01" "AT-028778-A" "AT-028778-02" "AT-028778-01-02" 11 11 2 3 787.5599365234375 3868.56005859375 18185.041015625 13528.919921875 0 0 2100 "AT-028778-02" "AT-028778-A" "AT-028778-01" "AT-028778-01-02" 11 11 2 3 236.27999877929687 46.19999694824219 5585.0400390625 5302.56005859375 0 0 2100 "AT-030660-01" "AT-030660-A" "" "" 11 11 2 1 652.199951171875 6.960000038146973 3627.599853515625 2968.43994140625 0 0 0 "AT-033787-01" "AT-033787-A" "AT-033787-02" "AT-033787-01-02" 11 11 2 3 0 712.5599975585938 8649.9599609375 7937.400390625 0 0 641.4675903320313 "AT-033787-02" "AT-033787-A" "AT-033787-01" "AT-033787-01-02" 11 11 2 3 3003.9599609375 3337.43994140625 23000.041015625 16658.640625 0 0 641.4675903320313 "AT-033812-01" "AT-033812-A" "" "" 11 11 1 1 577.4400024414063 2021.52001953125 13199.96484375 10601.0048828125 0 0 0 "AT-034868-01" "AT-034868-A" "" "" 11 11 1 1 945 15939.958984375 44210.3984375 27325.44140625 0 0 0 "AT-036830-01" "AT-036830-A" "AT-036830-02" "AT-036830-01-02" 11 11 2 3 2438.52001953125 7151.6396484375 29566.7109375 19976.55078125 0 0 16936.736328125 "AT-036830-02" "AT-036830-A" "AT-036830-01" "AT-036830-01-02" 11 11 2 3 1840.56005859375 9941.5205078125 42009.9609375 30227.87890625 0 0 16936.736328125 "AT-037076-01" "AT-037076-A" "" "" 11 11 1 1 420 954.3599853515625 9599.9658203125 8225.60546875 0 0 0 "AT-038211-01" "AT-038211-A" "" "" 11 11 2 1 0 722.1600341796875 8310 7587.83984375 0 0 16000 "AT-039523-01" "AT-039523-A" "" "" 11 11 1 1 804.3599853515625 3900.47998046875 18384.2734375 13679.4345703125 0 0 0 "AT-042070-01" "AT-042070-A" "AT-042070-02" "AT-042070-01-02" 11 11 2 3 652.199951171875 0 5799.9599609375 5147.759765625 0 0 850 "AT-042070-02" "AT-042070-A" "AT-042070-01" "AT-042070-01-02" 11 11 2 3 782.1600341796875 0 849.9600219726563 67.80001831054688 0 0 850 "AT-042307-01" "AT-042307-A" "" "" 11 11 1 1 209.04000854492187 13.680000305175781 4832.0400390625 4609.3203125 0 0 0 "AT-043192-01" "AT-043192-A" "AT-043192-02" "AT-043192-01-02" 11 11 2 2 525 1665.840087890625 12000.0341796875 9809.1943359375 4741.7998046875 4025.63818359375 0 "AT-043885-01" "AT-043885-A" "AT-043885-02" "AT-043885-01-02" 11 11 2 3 813.719970703125 3977.159912109375 18601.1640625 13810.28515625 0 0 0 "AT-043885-02" "AT-043885-A" "AT-043885-01" "AT-043885-01-02" 11 11 2 3 0 .23999999463558197 1.2000000476837158 .9599999785423279 0 0 0 "AT-044494-01" "AT-044494-A" "AT-044494-02" "AT-044494-01-02" 11 11 2 3 2880.719970703125 4834.68017578125 26409.92578125 18694.525390625 0 0 0 "AT-044494-02" "AT-044494-A" "AT-044494-01" "AT-044494-01-02" 11 11 2 3 983.1600341796875 2.5199999809265137 4629.92529296875 3644.24560546875 0 0 0 "AT-044710-01" "AT-044710-A" "" "" 11 11 1 1 735 4867.31982421875 19523.96484375 13921.64453125 0 0 0 "AT-046962-01" "AT-046962-A" "" "" 11 11 1 1 1542.840087890625 5182.31982421875 22950.033203125 16224.873046875 0 0 0 end label values country country label def country 11 "Austria", modify label values language language label def language 11 "German (at)", modify label values htype Hrespt label def Hrespt 1 "1 S", modify label def Hrespt 2 "1 CNRP", modify label def Hrespt 3 "1 C2R", modify label def Hrespt 5 "Multi S", modify
Comment