Copy multiple variables

[BE Wahlin]

I have a question which might appear silly. I have hundreds of variables named *1001 and hundreds named *1003. The numbers are for two different weeks (1001 and 1003), and the wildcard stands in for many different things, such as hemoglobin levels and IL-6 levels, i e hb1001, il61001, hb1003, il61003.

In all cases the *1001 variables are the first recorded when they exist, but some patients only have measurements at week 1003 (the *1003 variables). I simply want to mass create a set of variables named *1000 with the *1001 values when they exist, and the rest with *1003. I could not find a way to perform a mass generation of variables. Forgive me for being so dim, but I have searched and googled to no avail. I would be thankful for your help.

Björn Wahlin, MD PhD

[Rich Goldstein]

you don't say what the name prefixes are or whether they follow any pattern; if they do follow a simple pattern something like the following will work (where you replace my "pattern" with the variable names or their pattern):

foreach ln in pattern {
gen `ln'1000 = `ln'1001
replace `ln'1000 = `ln'1003 if `ln'1000==.
}

[BE Wahlin]

Thank you for your kind response! No, the prefixes don't have any pattern, they are many different cytokines and other measurements, such as hemoglobin and il-6 (i e hb1001, il61001, hb1003, il61003 and so on).

I really cannot find the solution. I mean it's easy to

rename *1001 *1000

Why can't I just

gen *1000 = *1001
replace *1000 = *1003 if *1001==."

If you catch my drift?

[Nick Cox]

Well, because Stata is not written that way.... But don't feel dim. I don't think this problem is at all easy. This sketch assumes that not existing means missing values, not that no such variable exists (although the first block of code is more general).

local stubs
foreach x in 1001 1003 {
    foreach v of var *`x' {
        local stub : subinstr local v "`x'" "", all
        local stubs `stubs' `stub'
    }
}

local stubs : list uniq stubs

foreach s of local stubs {
     gen `s'1000 = cond(missing(`s'1001), `s'1003, `s'1001)
}

[William Lisowski]

Here is some sample technique that may point you in a useful direction.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(cat1001 cat1003 dog1001 dog1003)
11 31 11 31
12 32  . 32
end

quietly ds *1001
local prefixes `r(varlist)'
local prefixes : subinstr local prefixes "1001" "", all
foreach p of local prefixes {
    generate `p'1000 = cond(`p'1001==., `p'1003, `p'1001)
}
list, clean

. list, clean

       cat1001   cat1003   dog1001   dog1003   cat1000   dog1000  
  1.        11        31        11        31        11        11  
  2.        12        32         .        32        12        32

Added in edit: this crossed with Nick's more cautious code. Mine will not be robust to any variable which was measured in only one week. But then, neither would the code in post #3.

[BE Wahlin]

Thank you both for your kind responses, Nick and William!

I think this will work, because the variables should exist (only a subset of patients would have missing values) so the variables should be there. I will try this tonight when I'll have time to do some research. COVID-19 has eaten up my research time.

[Nick Cox]

Here is a sketch of pessimistic code for if one or other variable does not exist. Nothing tested.

foreach s of local stubs {
    capture confirm var `s'1001 
    local exists1 = _rc == 0 
    capture confirm var `s'1003 
    local exists3 = _rc == 0 
     
    if `exists1' & `exists3' { 
        gen `s'1000 = cond(missing(`s'1001), `s'1003, `s'1001)
    }
    else if `exists1' { 
        gen `s'1000 = `s'1001
    }
    else if `exists3' { 
        gen `s'1000 = `s'1003 
    }
}

[BE Wahlin]

My dear helpers, I am almost on the verge of tears. I just ran Nick's code and it worked straight away. A miniscule sample of my newly created variables:

. li patid  il6x1000 il6x1001 il6x1003 hb1000 hb1001 hb1003


    +----------------------------------------------------------------------+
     |    patid   il6x1000   il6x1001   il6x1003   hb1000   hb1001   hb1003 |
     |----------------------------------------------------------------------|
  1. | FLCTRL07    1.17515          .    1.17515        .        .        . |
  2. | FLCTRL08    1.08763          .    1.08763        .        .        . |
  3. | FLCTRL09    2.16959          .    2.16959        .        .        . |
  4. | FLCTRL10    1.54069          .    1.54069        .        .        . |
  5. |  IFNFL02    2.67308    2.67308    2.86646      148      148      146 |
     |----------------------------------------------------------------------|
  6. |  LENFL01    2.22139    2.22139    2.11655      137      137      136 |
  7. |  LENFL02     2.7539     2.7539    4.06505      117      117       97 |
  8. |  RITFL01    4.38186          .    4.38186      121        .      121 |
  9. |  RITFL02    1.82643          .    1.82643      127        .      127 |
 10. |  RITFL03    2.87143          .    2.87143      122        .      122 |
     |----------------------------------------------------------------------|
 11. |  RITFL04    2.73163          .    2.73163      135        .      135 |
 12. |  RITFL05    2.80449          .    2.80449      152        .      152 |
 13. |  RITFL06    3.10057          .    3.10057      133        .      133 |
 14. |  RITFL07    3.72463          .    3.72463      115        .      115 |
 15. |  RITFL08     2.3455          .     2.3455      142        .      142 |
     |----------------------------------------------------------------------|

I thank you with all my heart!

(I did not get Nick's second piece of code to work, but that wasn't needed.)

[Nick Cox]

Good to hear this. #7 wasn’t intended as complete code, but as a replacement for the last block of #4.