Generate new var

[Jade Li]

Hi all,

I have a question about my data. I would like to generate a new variable which I call here MG_new. There have been changes in the sec of the ID variable. So I need the HG also to be changed.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 id_comp byte(sec1 mg sec_new)
"A" 50 4 60
"B" 61 1 17
"C" 10 2 20
"D" 17 3 17
"E" 20 5 50
"F" 60 1 61
"G" 12 5 10
end

I used this code and it gave me something not what I wanted (I am aware that it is not the right code but was trying to figure out a way to do this in a quick way!)

 gen MG_new=.
 replace MG_new=mg if sec1==sec_new

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 id_comp byte(sec1 mg sec_new) float MG_new
"A" 50 4 60 .
"B" 61 1 17 .
"C" 10 2 20 .
"D" 17 3 17 3
"E" 20 5 50 .
"F" 60 1 61 .
"G" 12 5 10 .
end

The optimal result I could get is something like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 id_comp byte(sec1 mg sec_new) float MG_new
"A" 50 4 60 1
"B" 61 1 17 3
"C" 10 2 20 5
"D" 17 3 17 3
"E" 20 5 50 4
"F" 60 1 61 1
"G" 12 5 10 2
end

I have a very large dataset, and it is kinda cumbersome to use "replace .. if" for every single observation I want to change.

Thanks for your answers in advance!

Best wishes
JLi

[Ken Chui]

Have a confusion here:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 id_comp byte(sec1 mg sec_new) float MG_new
"A" 50 4 60 1
"B" 61 1 17 3
"C" 10 2 20 5
"D" 17 3 17 3
"E" 20 5 50 4
"F" 60 1 61 1
"G" 12 5 10 2
end

The question did not explain where 1, 3, 5, [ ], 4, 1, 2 for all other 6 cases in MG_new come from.

[Jade Li]

Hi Ken,

These come from MG. For example, taking Id A, Sec number 50 has been changed to 60 in the data. Then I must also change MG by creating a new var myself. For sec 50, MG was 4. After being converted to 60 in the data, I have to generate MG_new based on MG. sec1= 60 had MG= 1 (see id F), so MG_new for sec_new 60 must also be 1 and not 4.

Does this help?

Thanks

[Ken Chui]

clear
input str1 id_comp byte(sec1 mg sec_new)
"A" 50 4 60
"B" 61 1 17
"C" 10 2 20
"D" 17 3 17
"E" 20 5 50
"F" 60 1 61
"G" 12 5 10
end

preserve
keep sec1 mg
collapse (mean) mg, by(sec1)
rename sec1 sec_new
rename mg MG_new
save temp_file, replace
restore

merge m:1 sec_new using temp_file
keep if _merge == 3
drop _merge

list, sep(0)

Results:

     +----------------------------------------+
     | id_comp   sec1   mg   sec_new   MG_new |
     |----------------------------------------|
  1. |       G     12    5        10        2 |
  2. |       B     61    1        17        3 |
  3. |       D     17    3        17        3 |
  4. |       C     10    2        20        5 |
  5. |       E     20    5        50        4 |
  6. |       A     50    4        60        1 |
  7. |       F     60    1        61        1 |
     +----------------------------------------+

Notice that this only helps if everyone values in sec_new appears in sec1, and assuming all "mg" values within each level of sec_1 are unique.

In actual practice, I'd suggest to get the actual coding conversion from sec to mg, and actually apply that algorithm rather than using merging. That would be more clear.

[Jade Li]

Thank you so much! It worked out well. Your assumption is valid: values in sec_new appear in sec1, and yes all "mg" values within each level of sec_1 are unique.