Hierarchical var ordering based on var names

[Liz Broom]

Hi, I was hoping somebody could help me - I am trying to hierarchically order var based on different portions of their names. I have data comprising var which are named according to the protein measured, the specific peptide assayed, the type of measurement and in some cases a suffix to denote a measure of variance. The data is in long format which is necessary due to this dataset being part of a much larger dataset where each observation corresponds to a particular ID.

For example, protein 'a' may have three different peptides measured ('1', '2' and '3'), each peptide would have three types of measurement ('i', 'ii' and 'iii'), and for each of those types of measurement, there would also be a measure of variance, denoted by the suffix '_cv'. This would correspond to 18 var as follows (in the desired order): a_1_i a_1_i_cv a_1_ii a_1_ii_cv a_1_iii a_1_iii_cv a_2_i a_2_i_cv a_2_ii a_2_ii_cv a_2_iii a_2_iii_cv a_3_i a_3_i_cv a_3_ii a_3_ii_cv a_3_iii a_3_iii_cv

I want the var to be ordered first by protein, then peptide, then measurement type, finally with each _cv value next to the measurement. In reality, the data is not ordered as I wish (all _cv values are at the end as were merged from a separate dta).

Here is an example dataset containing two proteins, a and b, with var in a random order (the actual dataset contains a large number of observations and var):

clear

input plate id b_2_i b_2_i_cv b_2_iii b_1_ii b_1_ii_cv b_1_iii b_1_iii_cv a_3_ii_cv b_2_ii a_3_iii b_2_ii_cv a_1_ii b_3_i_cv b_3_ii b_3_ii_cv b_3_iii b_3_iii_cv a_3_i a_3_i_cv b_2_iii_cv b_3_i a_2_ii_cv a_2_iii a_2_iii_cv a_3_iii_cv b_1_i b_1_i_cv a_3_ii a_1_i a_1_i_cv a_1_ii_cv a_1_iii a_1_iii_cv a_2_i a_2_i_cv a_2_ii
1 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
end

I have the following code to list all var names excluding the variables plate and id:

local vars `r(varlist)'
unab omit: plate id
local measures : list vars - omit

However, I am unsure how to reference the specific protein/peptide/measurement type for ordering?

I would be very grateful for some guidance!

Many thanks,
Liz

[Andrew Musau]

This is hard to code, but given that you already have the list in place, what changes appears to be the prefix. Then you can have a starting list and specify different prefixes.

local a_list  a_1_i a_1_i_cv a_1_ii a_1_ii_cv a_1_iii a_1_iii_cv a_2_i a_2_i_cv a_2_ii a_2_ii_cv a_2_iii a_2_iii_cv a_3_i a_3_i_cv a_3_ii a_3_ii_cv a_3_iii a_3_iii_cv
local list `a_list'
foreach l in b c d{
    local list "`list' `=ustrregexra("`a_list'", "a\_", "`l'\_")'"
}
display "`list'"

Res.:

. display "`list'"
a_1_i a_1_i_cv a_1_ii a_1_ii_cv a_1_iii a_1_iii_cv a_2_i a_2_i_cv a_2_ii a_2_ii_cv a_2_iii a_2_iii_cv a_3_i a_3_i_cv a_3_i
> i a_3_ii_cv a_3_iii a_3_iii_cv b_1_i b_1_i_cv b_1_ii b_1_ii_cv b_1_iii b_1_iii_cv b_2_i b_2_i_cv b_2_ii b_2_ii_cv b_2_ii
> i b_2_iii_cv b_3_i b_3_i_cv b_3_ii b_3_ii_cv b_3_iii b_3_iii_cv c_1_i c_1_i_cv c_1_ii c_1_ii_cv c_1_iii c_1_iii_cv c_2_i
>  c_2_i_cv c_2_ii c_2_ii_cv c_2_iii c_2_iii_cv c_3_i c_3_i_cv c_3_ii c_3_ii_cv c_3_iii c_3_iii_cv d_1_i d_1_i_cv d_1_ii d
> _1_ii_cv d_1_iii d_1_iii_cv d_2_i d_2_i_cv d_2_ii d_2_ii_cv d_2_iii d_2_iii_cv d_3_i d_3_i_cv d_3_ii d_3_ii_cv d_3_iii d
> _3_iii_cv

Then you can use ds and extended macro functions to eliminate nonexisting elements and order the variables.

help ds
help macro

[Liz Broom]

Hi Andrew, thanks so much for getting back to me!

That's a great solution and works well with the example data, however in reality a/b/c/d and 1/2/3 are each random combinations of letters and numbers, and the dataset has many variables so it would be impracticle to list each.

Is there a way of extracting the first X characters before the first "_" from each var name and saving these prefixes as a local, then extracting the middle section of the name between the first and second "_", and then using these 2 lists with your solution above?

I had a go at coding the extraction of the first prefix (a/b/c/d in the example dataset), but got the error: substr not allowed.

ds plate id, not
local vars `r(varlist)'
foreach var in `vars' {
local prefix1 : substr("`var'", 1, strpos("`var'","_"))
local prefix1_list "`prefix1_list' `prefix1'"
}

Many thanks,
Liz

[Mike Lacy]

A small point: The macro extended list function syntax possibilities do not happen to include -substr- even though they do include -subinstr-. However, you don't need such to extract the prefix as you would like, by simply replacing ":" with "=".

local prefix1 = substr("`var'", 1, strpos("`var'","_"))

[Andrew Musau]

Is there a way of extracting the first X characters before the first "_" from each var name and saving these prefixes as a local, then extracting the middle section of the name between the first and second "_", and then using these 2 lists with your solution above?

This would make sense as long as an alphabetic ordering is justified.

clear
input plate id b_2_i b_2_i_cv b_2_iii b_1_ii b_1_ii_cv b_1_iii b_1_iii_cv a_3_ii_cv b_2_ii a_3_iii b_2_ii_cv a_1_ii b_3_i_cv b_3_ii b_3_ii_cv b_3_iii b_3_iii_cv a_3_i a_3_i_cv b_2_iii_cv b_3_i a_2_ii_cv a_2_iii a_2_iii_cv a_3_iii_cv b_1_i b_1_i_cv a_3_ii a_1_i a_1_i_cv a_1_ii_cv a_1_iii a_1_iii_cv a_2_i a_2_i_cv a_2_ii
1 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
end

local prefixes1
local prefixes2
foreach var of varlist *{
   if ustrregexm("`var'", "(^[^\_])[\_]{1}([^\_])[\_]{1}.*"){
      local prefixes1 `prefixes1' `=ustrregexs(1)'
      local prefixes2 `prefixes2' `=ustrregexs(2)'
   }
}
local prefixes1: list uniq prefixes1
local prefixes1: list sort prefixes1
local prefixes2: list uniq prefixes2
local prefixes2: list sort prefixes2

local list
foreach p1 of local prefixes1{
    foreach p2 of local prefixes2{
        local list "`list' `p1'_`p2'"
    }
}

local ending  "_i _i_cv _ii _ii_cv _iii _iii_cv"
local order
foreach l of local list{
    foreach e of local ending{
        capture confirm variable `l'`e'
        if !_rc{
            local order `order' `l'`e'
        }
    }
}
order `order'

Res.:

. di "`order'"
a_1_i a_1_i_cv a_1_ii a_1_ii_cv a_1_iii a_1_iii_cv a_2_i a_2_i_cv a_2_ii a_2_ii_cv a_2_iii a_2_iii_cv a_3_i a_3_i_cv a_3_
> ii a_3_ii_cv a_3_iii a_3_iii_cv b_1_i b_1_i_cv b_1_ii b_1_ii_cv b_1_iii b_1_iii_cv b_2_i b_2_i_cv b_2_ii b_2_ii_cv b_2_
> iii b_2_iii_cv b_3_i b_3_i_cv b_3_ii b_3_ii_cv b_3_iii b_3_iii_cv