List unique (string) values of a variable in a table of multiple columns

[Castor Comploj]

I have a survey with participants from certain countries answering the question. I want to make a list of these unique names in a table of multiple columns. I apologize if this was asked before, I could not find it. The following table should be created and exported from within Stata.

[Nick Cox]

help levelsof

[Bjarte Aagnes]

One way to get a docx table from the result of levelsof:

clear all
sysuse auto
levelsof make // example data

local ncols 5 // wanted # of cols

mata:  

    names = tokens(st_global("r(levels)"))

    while (mod(length(names),`ncols')) {
        
        names = ( names , " " )
    }
    
    names = rowshape(names, `ncols')'  
    
end

tempname tmpframe  
frame create `tmpframe'

frame `tmpframe' {

    getmata(c*)=names

    putdocx begin

        putdocx table tab1 = data(c?), ///
            layout(autofitcontents) border(all,nil) ///
            border(top, single) border(bottom, single)
            
        putdocx table tab1(1,.), border(bottom, single)
        
    putdocx save tab1.docx, replace
}

[Bjarte Aagnes]

adding to #3 the mata matrix can be used directly by putdocx table, then the complete example is:

clear all
sysuse auto
levelsof make // example data

local ncols 5 // wanted # of cols

mata:  

    names = tokens(st_global("r(levels)"))

    while ( mod(length(names),`ncols') ) {
        
        names = (names , " ")
    }
    
    names = rowshape(names, `ncols')'  
    
end

putdocx begin

    putdocx table tab1 = mata(names) , ///
        layout(autofitcontents) border(all,nil) ///
        border(top, single) border(bottom, single)
                
    putdocx table tab1(1,.), border(bottom, single)
            
putdocx save tab1.docx, replace

[Bjarte Aagnes]

Alternative using collect get (with final layout adjustments in putdocx):

clear all
sysuse auto

qui levelsof make // example data
tokenize `"`r(levels)'"' // locals 1 ... r(r) levels
local cols = 5 // wanted # of cols
local rows = ceil(r(r)/`cols')

collect clear
collect create tab1b

local level 0

forvalues c = 1/`cols' {
      
    forvalues r = 1/`rows' {
        
        local ++level // locals 1 ... r(r) levels
        
        collect get row`r' = `"``level''"' , tags(col[c`c']) 
    }
}

collect layout (result)(col)

* fix layout using docx 

collect style putdocx, layout(autofitcontents)
 
putdocx begin

    putdocx collect, name(tab1b) tablename(tab1b) 
    putdocx table tab1b(.,1), drop
    putdocx table tab1b(.,1), border(left, nil)  
    putdocx table tab1b(1,.), drop
    putdocx table tab1b(1,.), border(top, single)
    putdocx table tab1b(1,.), border(bottom, single)
    
putdocx save tab1b.docx, replace

[Bjarte Aagnes]

in #5 putdox was used for changing layout. Most of the changes can be done in collect. But, I cannot see how to hide the result dim, so below the result levels labels are set to PUNCTUATION SPACE. Thus, a first "empty" column (result dim) is left and will be exported when using collect export.

clear all sysuse auto

qui levelsof make // example data
tokenize `"`r(levels)'"' // locals 1 ... r(r) levels
local cols = 5 // wanted # of cols
local rows = ceil(r(r)/`cols')

collect clear
collect create tab1b

local level 0

forvalues c = 1/`cols' {
      
    forvalues r = 1/`rows' {
        
        local ++level // locals 1 ... r(r) levels
        
        collect get row`r' = `"``level''"' , tags(col[c`c'])
        
    }
}

collect layout (result)(col)

collect style header col, level(hide) title(hide)
collect style cell border_block, border(right, pattern(nil))
collect style cell result[row1], smcl(text) border(bottom)

forvalues r = 1/`rows' { // not able to hide dim, thus 
    
    collect label levels result row`r'  `= uchar(8200) ' , modify
}

collect preview

-----------------------------------------------------------------------------------
    AMC Concord      Cad. Seville       Fiat Strada        Olds 98 Pont. Grand Prix
-----------------------------------------------------------------------------------
      AMC Pacer    Chev. Chevette       Ford Fiesta Olds Cutl Supr    Pont. Le Mans
     AMC Spirit      Chev. Impala      Ford Mustang   Olds Cutlass    Pont. Phoenix
      Audi 5000      Chev. Malibu      Honda Accord  Olds Delta 88    Pont. Sunbird
       Audi Fox Chev. Monte Carlo       Honda Civic     Olds Omega   Renault Le Car
       BMW 320i       Chev. Monza Linc. Continental  Olds Starfire           Subaru
  Buick Century        Chev. Nova      Linc. Mark V  Olds Toronado    Toyota Celica
  Buick Electra        Datsun 200  Linc. Versailles    Peugeot 604   Toyota Corolla
  Buick LeSabre        Datsun 210         Mazda GLC    Plym. Arrow    Toyota Corona
     Buick Opel        Datsun 510      Merc. Bobcat    Plym. Champ        VW Dasher
    Buick Regal        Datsun 810      Merc. Cougar  Plym. Horizon        VW Diesel
  Buick Riviera        Dodge Colt     Merc. Marquis  Plym. Sapporo        VW Rabbit
  Buick Skylark    Dodge Diplomat     Merc. Monarch   Plym. Volare      VW Scirocco
   Cad. Deville      Dodge Magnum        Merc. XR-7 Pont. Catalina        Volvo 260
  Cad. Eldorado   Dodge St. Regis      Merc. Zephyr Pont. Firebird                 
-----------------------------------------------------------------------------------

(dashed lines above are solid lines in Stata result window)

[Leonardo Guizzetti]

The final -forvalues- loop in #6 can be replaced with the following line to hide the result dim labels.

collect style header result, level(hide)

[Castor Comploj]

Thanks. It works. For the reader: You copy the #6, replace make with your variable (here "country"), and add #7.