How to generate a alphabetic macro list using stata?

[BICHENG NIU]

Hi guys,

A crucial step in my programming is to generate a alphabetic macro list to locate the table position when I export the table to an excel. The problem is how to generate a macro list like:

A B C D.......Z AA AB AC ..... AZ BA BB.... BZ..... ZZ


Thanks!

[Fei Wang]

The macro below is sorted, not exactly in the order of #1, and hopefully it doesn't matter for your analysis.

clear
set obs `=26*27'
gen v = char(_n+64) in 1/26

forvalues i = 65/90 {
    forvalues j = 65/90 {
        replace v = char(`i') + char(`j') in `=(`i'-64)*26+(`j'-64)'
    }
}

levelsof v, l(v) clean
macro list _v

If the order does matter, please refer to the code below.

clear
set obs `=26*27'
gen v1 = char(_n+64) in 1/26
levelsof v1, l(v1) clean

gen v2 = ""
forvalues i = 65/90 {
    forvalues j = 65/90 {
        replace v2 = char(`i') + char(`j') in `=(`i'-64)*26+(`j'-64)'
    }
}
levelsof v2, l(v2) clean

local v = "`v1' " + "`v2'"
macro list _v

[Maarten Buis]

Here is a quick solution using the undocumented numtobase26() function in Mata:

. mata: st_local("coln", invtokens(numtobase26(1..702)))

. di "`coln'"
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN
> AO AP AQ AR AS AT AU AV AW AX AY AZ BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR BS
>  BT BU BV BW BX BY BZ CA CB CC CD CE CF CG CH CI CJ CK CL CM CN CO CP CQ CR CS CT CU CV CW C
> X CY CZ DA DB DC DD DE DF DG DH DI DJ DK DL DM DN DO DP DQ DR DS DT DU DV DW DX DY DZ EA EB
> EC ED EE EF EG EH EI EJ EK EL EM EN EO EP EQ ER ES ET EU EV EW EX EY EZ FA FB FC FD FE FF FG
>  FH FI FJ FK FL FM FN FO FP FQ FR FS FT FU FV FW FX FY FZ GA GB GC GD GE GF GG GH GI GJ GK G
> L GM GN GO GP GQ GR GS GT GU GV GW GX GY GZ HA HB HC HD HE HF HG HH HI HJ HK HL HM HN HO HP
> HQ HR HS HT HU HV HW HX HY HZ IA IB IC ID IE IF IG IH II IJ IK IL IM IN IO IP IQ IR IS IT IU
>  IV IW IX IY IZ JA JB JC JD JE JF JG JH JI JJ JK JL JM JN JO JP JQ JR JS JT JU JV JW JX JY J
> Z KA KB KC KD KE KF KG KH KI KJ KK KL KM KN KO KP KQ KR KS KT KU KV KW KX KY KZ LA LB LC LD
> LE LF LG LH LI LJ LK LL LM LN LO LP LQ LR LS LT LU LV LW LX LY LZ MA MB MC MD ME MF MG MH MI
>  MJ MK ML MM MN MO MP MQ MR MS MT MU MV MW MX MY MZ NA NB NC ND NE NF NG NH NI NJ NK NL NM N
> N NO NP NQ NR NS NT NU NV NW NX NY NZ OA OB OC OD OE OF OG OH OI OJ OK OL OM ON OO OP OQ OR
> OS OT OU OV OW OX OY OZ PA PB PC PD PE PF PG PH PI PJ PK PL PM PN PO PP PQ PR PS PT PU PV PW
>  PX PY PZ QA QB QC QD QE QF QG QH QI QJ QK QL QM QN QO QP QQ QR QS QT QU QV QW QX QY QZ RA R
> B RC RD RE RF RG RH RI RJ RK RL RM RN RO RP RQ RR RS RT RU RV RW RX RY RZ SA SB SC SD SE SF
> SG SH SI SJ SK SL SM SN SO SP SQ SR SS ST SU SV SW SX SY SZ TA TB TC TD TE TF TG TH TI TJ TK
>  TL TM TN TO TP TQ TR TS TT TU TV TW TX TY TZ UA UB UC UD UE UF UG UH UI UJ UK UL UM UN UO U
> P UQ UR US UT UU UV UW UX UY UZ VA VB VC VD VE VF VG VH VI VJ VK VL VM VN VO VP VQ VR VS VT
> VU VV VW VX VY VZ WA WB WC WD WE WF WG WH WI WJ WK WL WM WN WO WP WQ WR WS WT WU WV WW WX WY
>  WZ XA XB XC XD XE XF XG XH XI XJ XK XL XM XN XO XP XQ XR XS XT XU XV XW XX XY XZ YA YB YC Y
> D YE YF YG YH YI YJ YK YL YM YN YO YP YQ YR YS YT YU YV YW YX YY YZ ZA ZB ZC ZD ZE ZF ZG ZH
> ZI ZJ ZK ZL ZM ZN ZO ZP ZQ ZR ZS ZT ZU ZV ZW ZX ZY ZZ

More generally, if you want to know the name of the 66th variable, then you don't need that list:

. mata: st_local("coln", numtobase26(66))

. di "`coln'"
BN

[Mike Lacy]

Here's a relatively simple approach:

local stock = strupper("`c(alpha)'") // "A B C ..." would have been ok
local alist = "`stock'"
foreach c1 of local stock {
   foreach c2 of local stock {
      local alist = "`alist' `c1'`c2'"
   }
}
//
// Verbose display
foreach s of local alist {
   di "`s'"
}