Lookup values from a related variable

[Hans Schubert]

Good afternoon,

So I have a string variable in column 1 with a corresponding numeric variable in column 2.
The solution required is for the bottom observation in column 2.
At present it is blank - what is required is that it gets filled with the number that corresponds to the observation in column 1.
So here it needs to be filled with -.04692927 as this is the value that corresponds to column 1 (12h9) in this case.
There is only one value per code in column 1, so no need to worry about different values for identical codes.
I realise that I could use nested cond commands, but I have a lot of this data and would appreciate a faster method of doing it.
Thank you,
Hans

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 rank_IrLowProbLrC float mean_IrLowProbLr_nmfp
"11s1"    .18985137
"11s3"    .09992722
"11s6"   -.04241602
"11s2"    .15979142
"11s5"  .0024511705
"11s4"    .04912663
"11s9"   -.13586827
"11s7"   -.07778575
"11s9"   -.13586827
"11s8"   -.11544935
"11s11"  -.18941443
"12h1"    .11297596
"12h2"    .09036186
"12h3"    .07558914
"12h4"    .05233035
"12h6"   .014184715
"12h5"    .02813552
"12h7"  -.006742707
"12h8"  -.022842864
"12h10"  -.07057396
"12h9"   -.04692927
"12h11"  -.10291362
"12h12"  -.14404643
"12h2"    .09036186
"12h2"    .09036186
"12h1"    .11297596
"12h4"    .05233035
"12h6"   .014184715
"12h5"    .02813552
"12h7"  -.006742707
"12h8"  -.022842864
"12h12"  -.14404643
"12h10"  -.07057396
"12h11"  -.10291362
"12h9"   -.04692927
"12h9"            .
end

[daniel klein]

What do you mean by "fast"? Little code to type? Fast execution time? Something else?

This should work:

sort rank_IrLowProbLrC mean_IrLowProbLr_nmfp

// make sure your assumption about constant values per code is met
by rank_IrLowProbLrC (mean_IrLowProbLr_nmfp) : ///
    assert inlist(mean_IrLowProbLr_nmfp, float(mean_IrLowProbLr_nmfp[1]), .)

// insert first observed value per code into all observations
by rank_IrLowProbLrC (mean_IrLowProbLr_nmfp) : ///
    replace mean_IrLowProbLr_nmfp = mean_IrLowProbLr_nmfp[1]

[Hans Schubert]

Thanks. Didn't work. Just assigned the same value to the entire column.

[daniel klein]

Does work. Proof:

. clear

. input str5 rank_IrLowProbLrC float mean_IrLowProbLr_nmfp

     rank_Ir~C  mean_Ir~p
  1. "11s1"    .18985137
 (output omitted)
 36. "12h9"            .
 37. end

.
.
. sort rank_IrLowProbLrC mean_IrLowProbLr_nmfp

.
. // make sure your assumption about unique codes is met
. by rank_IrLowProbLrC (mean_IrLowProbLr_nmfp) : ///
>     assert inlist(mean_IrLowProbLr_nmfp, float(mean_IrLowProbLr_nmfp[1]), .)

.
. // insert first observed value per group
. by rank_IrLowProbLrC (mean_IrLowProbLr_nmfp) : ///
>     replace mean_IrLowProbLr_nmfp = mean_IrLowProbLr_nmfp[1]
(1 real change made)

.
. list

     +----------------------+
     | rank_I~C   mean_Ir~p |
     |----------------------|
  1. |     11s1    .1898514 |
  2. |    11s11   -.1894144 |
  3. |     11s2    .1597914 |
 (output omitted)
 34. |     12h9   -.0469293 |
 35. |     12h9   -.0469293 |
     |----------------------|
 36. |     12h9   -.0469293 |
     +----------------------+

I see 4 different values in mean_IrLowProbLr_nmfp for 4 different codes in rank_IrLowProbLrC.

If this is not what you want, explain better.

[Hans Schubert]

My apologies, Daniel. I forgot to sort the datasheet after I ran your code. Its working fine. Thanks very much.,