Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Comparing variables across rows

    Hi all,

    I'm not a frequent Stata user and got stuck on something that I think ought to be fairly simple. Perhaps someone here could help? I'm using Stata 16.0.

    I'd like to calculate associations (using Spearman's rank correlation) between different biomarkers that are measured within 60 days from each other. I have panel data with 10 biomarkers, some measured only once and some up to four times, and some are measured the same date and some not (see data below(I only included two biomarkers to make it simpler)). How can I make this calculation across rows? I assume I need to get biomarker1 to populate the same row as biomarker2 if the id is the same and the biomarker_date is within 60 days, but I don't understand how. Does anyone know?

    Many thanks!

    Best
    Gunnar



    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input float id double biomarker_date float(biomarker1 biomarker2)
    185              21805         .  772.1053
    117              21815         .  386.8421
    248              21910         .       280
    191              20135         .  303.1579
    273              20381         .  320.5263
    273              20167         .  283.6842
    265              20275         . 174.73685
    124              20290         .  652.6316
    258              20891         .         .
    258              20891         . 35.789474
    258  20891.57986111111         .         .
    258              21231         .  39.47368
    258              20986         .         .
    258  21231.48611111111         .         .
    258              21231         .         .
    258              20986         .         .
    258              21231         .         .
    258              20986  6.481458         .
    258              20891         .         .
    250         20954.5625         .         .
    250              20520  11.93801         .
    250              20954         .         .
    250              20520         .         .
    250              20954         .         .
    250              20520         .         .
    250              20520         .  461.0526
    250              20520         .         .
    250 20520.583333333336         .         .
    250              20954         .  362.6316
    250              20520         .         .
    250              20954         .         .
    173              21223         . 111.05264
    173              21223         .         .
    173              21223         .         .
    173              21223         .         .
    173              20849         .         .
    173 20544.479166666664         .         .
    173              20849         . 168.21053
    173              20849   2.76059         .
    173              21223  3.469453         .
    173              20849         .         .
    173              20508         .         .
    173              20544         .         .
    173  21223.46527777778         .         .
    173              20557         .   83.1579
    173              20849         .         .
    173              20544         .         .
    173              20849         .         .
    173              20508         .         .
    173              20508 3.8420894         .
    173              21223         .         .
    173              21669         . 137.89473
    190              20213         .  673.6842
    206              20053         . 361.57895
    214              20282         .  412.6316
    169              20242         . 331.57895
    197              20968         .         .
    197              21600         . 151.57895
    197              20943         .         .
    197              21600         .         .
    197              21293 14.454144         .
    197              20943         . 194.21053
    197              21293         .         .
    197              20968 14.237902         .
    197              21293         . 227.36842
    197              20943         .         .
    197 21293.458333333336         .         .
    197              21293         .         .
    197  20943.59027777778         .         .
    197              21293         .         .
    197              21600         .         .
    197              21293         .         .
    197              20968         .         .
    158              20107         . 110.52631
    225              20178         . 201.57895
    261              19935         .  726.3158
    133              20787         .         .
    133 21171.583333333336         .         .
    133              20787         . 431.57895
    133              21171         .  673.6842
    133              21076         .         .
    133              20559 18.342129         .
    133              20559         .         .
    133              21171         .         .
    133 20787.583333333336         .         .
    133              20787         .         .
    133              21171         .         .
    133              20559         .         .
    133              21076         .         .
    133              21076  38.20453         .
    133              20256         .  213.1579
    123              20555  18.10814         .
    123              20790         .         .
    123              21068         .         .
    123              20888 22.946787         .
    123              20888         .         .
    123              20790         .         .
    123              20555         .         .
    123              20888         .         .
    123              21068  27.05924         .
    end
    format %td biomarker_date

  • #2
    Install rangestat from SSC using
    Code:
    ssc install rangestat
    You need some extra code to do Spearman rather than Pearson which I can post within a day if no one else gets there first.

    Comment


    • #3
      On second thoughts: Use rangerun (SSC) installed similarly with custom code. Here's an example, with checks.

      Code:
      webuse grunfeld, clear 
      
      program myspear 
      spearman invest mvalue 
      gen spear_n = r(N)
      gen double spear_corr = r(rho)
      gen double spear_p = r(p)
      end
              
      rangerun myspear, interval(year -5 5) use(invest mvalue) by(company)
      
      . list invest mvalue spear_* in 1/20
      
           +---------------------------------------------------+
           | invest   mvalue   spear_n   spear_c~r     spear_p |
           |---------------------------------------------------|
        1. |  317.6   3078.5         6   .82857143   .04156268 |
        2. |  391.8   4661.7         7   .67857143   .09375025 |
        3. |  410.6   5387.1         8   .54761905   .16002564 |
        4. |  257.7   2792.2         9   .41666667   .26458605 |
        5. |  330.8   4313.2        10   .39393939   .25999777 |
           |---------------------------------------------------|
        6. |  461.2   4643.9        11   .49090909   .12520441 |
        7. |    512   4551.2        11   .42727273   .18994372 |
        8. |    448   3244.1        11   .34545455   .29808922 |
        9. |  499.6   4053.7        11   .54545455   .08265096 |
       10. |  547.5   4379.3        11          .3   .37008312 |
           |---------------------------------------------------|
       11. |  561.2   4840.9        11   .28181818    .4011449 |
       12. |  688.1   4900.9        11   .50909091   .10973723 |
       13. |  568.9   3526.5        11          .7   .01647098 |
       14. |  529.2   3254.7        11          .7   .01647098 |
       15. |  555.1   3700.2        11   .82727273   .00167697 |
           |---------------------------------------------------|
       16. |  642.9   3755.6        10   .86666667   .00117354 |
       17. |  755.9     4833         9         .95   .00008763 |
       18. |  891.2   4924.9         8   .95238095    .0002604 |
       19. | 1304.4   6241.7         7   .96428571   .00045415 |
       20. | 1486.7   5593.6         6   .94285714   .00480466 |
           +---------------------------------------------------+
      
      . spearman invest mvalue in 1/6
      
       Number of obs =       6
      Spearman's rho =       0.8286
      
      Test of Ho: invest and mvalue are independent
          Prob > |t| =       0.0416
      
      . spearman invest mvalue in 15/20
      
       Number of obs =       6
      Spearman's rho =       0.9429
      
      Test of Ho: invest and mvalue are independent
          Prob > |t| =       0.0048
      
      .

      Comment


      • #4
        Hi Nick,

        Thanks for your advice on ranger. Unfortunately I cannot get the code to work for my dataset though. I assume it's because my data differs from the grundfeld.dta. In grundfeld.dta the two variables are on the same row, but in my dataset the variables are on different rows, for example:

        id 1; biomarker1; 01oct2018
        id 1; biomarker2; 25oct2018
        id 2; biomarker1; 25apr2019
        id 2; biomarker2; 04mar2019
        id 2; biomarker2;15sep2019
        etc

        What I'd like to do is to run Spearman on biomarker 1 and biomarker 2 if they are no more than 60 days apart. Is it possible with ranger somehow, or with some other package/program?

        Best
        Gunnar

        Comment


        • #5
          Spearman correlation can be calculated only if values can be paired systematically. I have to confess that I don’t understand how you imagine that can be done here.

          Comment


          • #6
            My idea is to pair variables that are measured within 60 days from each other. I know I can pair them in Excel but it's somewhat cumbersome, so I thought Stata perhaps had a simpler solution?

            Comment

            Working...
            X