Weird rename rule

[Esteban Jara]

Hello. I am trying to automate the import of some Excel files with a somewhat complicated structure. I need to rename the variables (which currently have names A, B, C, D, etc.) to the value of the 4th row, but this row includes values that may be repeated throughout the row, and also includes null values. Does anyone have any tips on how to solve this problem? Thank you

[Jared Greathouse]

Data example please!!!!!

[Esteban Jara]

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 A str17(B C) str12 D str8 E str1 F str17(G H) str12 I str17 J str1 K str17(L M) str12 N
"Local"     "Formato"    ""       ""        ""        "." ""                  ""       ""       ""        "." ""            ""       ""      
"Guias"     "1-2-3-4"    ""       ""        ""        "." ""                  ""       ""       ""        "." ""            ""       ""      
""          "VACUNO"     ""       ""        ""        "." "POLLOS"            ""       ""       ""        "." "CERDO"       ""       ""      
"FECHA"     "COMPRA"     "Kg"     "VENTA"   "kg"      "." "COMPRA"            "kg"     "VENTA"  "kg"      "." "COMPRA"      "kg"     "VENTA" 
"SALDO"     "6384186"    "1206"   ""        ""        "0" "6530161"           "2469"   ""       ""        "." "6415791"     "1705"   ""      
""          ""           ""       ""        ""        "." ""                  ""       ""       ""        "." ""            ""       ""      
"01apr2024" "2554930"    "565"    "857290"  "165,962" "." "1191470,5"         "550,9"  "621350" "159,096" "." ""            ""       "274700"
"02apr2024" "1429821,81" "244,4"  "819130"  "123,526" "." "10119522"          "2820"   "549200" "132,698" "." "10144929,69" "2841,7" "266460"
"03apr2024" "1381457"    "324,44" "1030920" "136,978" "." ""                  ""       "482450" "127,866" "." ""            ""       "453620"
"04apr2024" "1817844"    "402"    "1202990" "166,64"  "." "675233,1499999999" "331,95" "601310" "146,69"  "." "150600,45"   "42,9"   "454730"
end

------------------ copy up to and including the previous line ------------------

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 A str17(B C) str12 D str8 E str1 F str17(G H) str12 I str17 J str1 K str17(L M) str12 N
"Local"     "Formato"    ""       ""        ""        "." ""                  ""       ""       ""        "." ""            ""       ""      
"Guias"     "1-2-3-4"    ""       ""        ""        "." ""                  ""       ""       ""        "." ""            ""       ""      
""          "VACUNO"     ""       ""        ""        "." "POLLOS"            ""       ""       ""        "." "CERDO"       ""       ""      
"FECHA"     "COMPRA"     "Kg"     "VENTA"   "kg"      "." "COMPRA"            "kg"     "VENTA"  "kg"      "." "COMPRA"      "kg"     "VENTA" 
"SALDO"     "6384186"    "1206"   ""        ""        "0" "6530161"           "2469"   ""       ""        "." "6415791"     "1705"   ""      
""          ""           ""       ""        ""        "." ""                  ""       ""       ""        "." ""            ""       ""      
"01apr2024" "2554930"    "565"    "857290"  "165,962" "." "1191470,5"         "550,9"  "621350" "159,096" "." ""            ""       "274700"
"02apr2024" "1429821,81" "244,4"  "819130"  "123,526" "." "10119522"          "2820"   "549200" "132,698" "." "10144929,69" "2841,7" "266460"
"03apr2024" "1381457"    "324,44" "1030920" "136,978" "." ""                  ""       "482450" "127,866" "." ""            ""       "453620"
"04apr2024" "1817844"    "402"    "1202990" "166,64"  "." "675233,1499999999" "331,95" "601310" "146,69"  "." "150600,45"   "42,9"   "454730"
end

foreach var of varlist *{
    if "`=`var'[4]'"=="."{
        drop `var'
    }
    cap rename `var' `=`var'[4]'_`var'
}
rename *, lower
rename fecha* fecha
ds fecha, not
local i 0
foreach var in `r(varlist)'{
    local++i
    local j= ceil(`i'/2)
    rename `var' `=ustrregexra("`var'", "(.*)_.*", "$1`j'")'
}
rename (compra* venta*) value=
drop in 1/6
reshape long value, i(fecha) j(transaction) string
reshape long kg, i(fecha transaction) j(which)
keep if real(ustrregexra(transaction, "[^\d]", ""))==which
replace transaction= ustrregexra(transaction, "[\d]", "")
sort fecha which

Res.:

. l, sepby(fecha)

     +------------------------------------------------------------+
     |     fecha   transa~n   which        kg               value |
     |------------------------------------------------------------|
  1. | 01apr2024     compra       1       565             2554930 |
  2. | 01apr2024      venta       2   165,962              857290 |
  3. | 01apr2024     compra       3     550,9           1191470,5 |
  4. | 01apr2024      venta       4   159,096              621350 |
  5. | 01apr2024     compra       5                               |
     |------------------------------------------------------------|
  6. | 02apr2024     compra       1     244,4          1429821,81 |
  7. | 02apr2024      venta       2   123,526              819130 |
  8. | 02apr2024     compra       3      2820            10119522 |
  9. | 02apr2024      venta       4   132,698              549200 |
 10. | 02apr2024     compra       5    2841,7         10144929,69 |
     |------------------------------------------------------------|
 11. | 03apr2024     compra       1    324,44             1381457 |
 12. | 03apr2024      venta       2   136,978             1030920 |
 13. | 03apr2024     compra       3                               |
 14. | 03apr2024      venta       4   127,866              482450 |
 15. | 03apr2024     compra       5                               |
     |------------------------------------------------------------|
 16. | 04apr2024     compra       1       402             1817844 |
 17. | 04apr2024      venta       2    166,64             1202990 |
 18. | 04apr2024     compra       3    331,95   675233,1499999999 |
 19. | 04apr2024      venta       4    146,69              601310 |
 20. | 04apr2024     compra       5      42,9           150600,45 |
     +------------------------------------------------------------+

[Esteban Jara]

Wow, thank you very much for the effort Andrew Musau. It looks complicated but I'll try to apply it. Greetings!