Stata odbc get column comment as label

user_6535324 0
Viewed 79

I am trying to access data from impala sql into Stata:

clear all
#delimit;
odbc load, exec("
SELECT column_name
data_base_name.table_name
")
dsn("<my dsn>")
user("")
password("");
#delimit cr

The query works just fine, but the labels are empty. Is there any way to get those into stata?

I created comments the following way:

ALTER TABLE  data_base_name.table_name
CHANGE column_name column_name string comment 'test comment';

So when I import to Stata the variable name column_name should have the label "test comment". Is this possible? Or any other way (other than comment I mean)?

impala stata sql
1 Answers

help odbc doesn't mention anything about comments, odbc describe doesn't return column comments and there is no mention in the manual about accessing comments, so there doesn't seem to be a straightforward way. Here's a workaround, using moremata from SSC.

odbc load, exec("DESCRIBE data_base_name.table_name") dsn("my dsn") clear
list

// Get variable names and comment labels
* ssc install moremata
mata: st_local("vars", invtokens(st_sdata(., ("name"))'))
mata: st_local("labels", mm_invtokens(st_sdata(., ("comment"))'))
local n_vars = _N

// Get actual data and label the variables
odbc load, exec("SELECT * FROM data_base_name.table_name") dsn("my dsn") clear
forval i = 1/`n_vars' {
    cap confirm variable `:word `i' of `vars''
    if !_rc label var `:word `i' of `vars'' "`:word `i' of `labels''"
}
user_117702860
Related