Changing the value of one variable depending on the value of another

[JR Rea]

[CODE]
* Example generated by -dataex-. For more info, type help dataex
clear
input str7 MCSID byte(GC_L_GCGD GC_L_GCDB)
"M10016V" 6 2
"M10016V" 0 -1
"M10016V" 6 2
"M10016V" 6 2
"M10016V" 0 -1
"M10016V" 0 -1
"M10016V" 0 -1
"M10016V" 0 -1
"M10016V" 5 2
"M10016V" 0 -1
"M10016V" 0 -1
"M10016V" 0 -1
"M10016V" 7 1
"M10016V" 0 -1
"M10016V" 0 -1
"M10016V" 7 2
"M10016V" 8 2
"M10016V" 0 -1
"M10016V" 0 -1
"M10016V" 7 2
"M10044Z" 6 2
"M10044Z" 6 2
"M10044Z" 0 -1
"M10044Z" 0 -1


From the dataset above I am trying to create a final figure based on scores for exam results. However, if GC_L_GCDB==1 then I need the corresponding score on GC_L_GCGD to double. How do I then total the score for each MCSID to create one final score for each MCSID such that it does not appear multiple times? Many thanks, JE Rea

[Carlo Lazzaro]

JR:
1) I find the relationship between the two variables GC_L_GCDB and GC_L_GCGD totally unclear;
2) in addition, do you mean to -collapse- the total score for each -MCSID-?

[Nick Cox]

I note @Carlo Lazzaro's points and tend to agree. Meanwhile, here is some technique.

.

 egen wanted = total(cond(GC_L_GCDB == -1, 2 * GC_L_GCGD, GC_L_GCGD )), by(MCSID)

. 
. tabdisp MCSID, c(wanted)

----------------------
    MCSID |     wanted
----------+-----------
  M10016V |         52
  M10044Z |         12
----------------------

I guessed that minus 1 is special, but if it is 1 as you say, change code accordingly.

If this does not help (enough) then please tell us what the right answers are.

egen tag = tag(MCSID) 

is a way to tag just one observation for each identifier.

[JR Rea]

Firstly, GC_L_GCDB records the score obtained relating to each subject examined. GC_L_GCDB states whether the exam is 'double award' in which case the score obtained needs to be doubled. Only when GC_L_GCDB==1 does this indicate that the corresponding GC_L_GCDB score should be doubled. For MCSID==M!0016V there is only 1 double award exam for which the student scored 7. This needs to be doubled to 14 before I total all the individual GC_L_GCDB scores for each MCSID. Once this has been calculated I want to produce 1 MCSID with 1 total score as opposed to the 20 rows which are currently depicted. Thank you

[Nick Cox]

OK, so -1 should be 1 in the code I posted.

[JR Rea]

Apologies, GC_L_GCGD records the score obtained relating to each subject examined. GC_L_GCDB states whether the exam is 'double award' in which case the score obtained needs to be doubled. However, this code is giving me an answer of 28 when it should be 59. This is made up of the total of GC_L_GCGD for each MCSID, but doubling the figure where GC_L_GCDB==1. 6+6+6+5+7(2)+7+8+7=59

[Clyde Schechter]

by MCSID, sort: egen wanted = total(GC_L_GCGD*(1+(GC_L_GCDB == 1)))

[JR Rea]

Thank you, after I recoded parts of GC_L_GCDB this code worked. Many thanks, much appreciated.