I wrote:
If you want to reconstruct the value labels using the same numerical
correspondence that the database uses, then you will need two trips to the
database . . .
----------------------------------------------------------------------------
It's not actually necessary to retrieve the value labels and data table
separately. You can -odbc load- the primary key-foreign key columns along
with loading the data table and the lookup table's value labels, all in one
fell swoop; just include either the primary key (lookup table) or foreign
key (data table) column in the SQL statement's SELECT list.
To answer Benjamin A Lopman's original question, there's an illustration in
the do-file below of how to do what he wants from within Stata, given an
Access database received from a colleague. (He mentions using
Stat/Transfer, and so under the assumption that he has it, I've used
Stat/Transfer, called from Stata, just in order to create a fictitious
Access database for use in the illustration.) The illustration requires you
to set up the DSN for an Access database called "Demo.mdb" so that you can
use Stata's -odbc- commands. The do-file first creates the lookup table
(PatientTypes) and then the fictional dataset's data table (Patients) in a
new Access database called "Demo.mdb" via Stat/Transfer. Then the
illustrates begins of how to (re)construct value labels in Stata from the
Access database while preserving the numerical correspondence between values
and value labels.
The SQL statement in the -odbc command- will wrap during e-mail handling and
list-posting (I don't know of any way to do continuation lines in the SQL
statement in order to break it up so as avoid wrapping), and so you'll need
to manually remove the line wraps before attempting to execute the do-file..
Joseph Coveney
// Creating the fictional-data Access database for illustration
clear
set obs 2 // The lookup table
generate byte patient_type_id = _n
generate str patient_type = "In Patient"
replace patient_type = "Out Patient" in 2
outputst "a:\Demo.mdb" "/tPatientTypes"
clear
set obs 100 // The data table
generate byte patient_id = _n
generate byte patient_type_id = ceil(uniform() *2)
slist in 1/10, noobs
outputst "a:\Demo.mdb" "/tPatients"
*
* The illustration begins here
*
clear
// Check for e-mail & Statalist wrapping of the next couple of lines
odbc load, exec("SELECT patient_id, a.patient_type_id, patient_type FROM
Patients AS a, PatientTypes AS b WHERE a.patient_type_id =
b.patient_type_id") dialog(complete) dsn(Demo) sqlshow
replace patient_type = ltrim(rtrim(patient_type))
compress
// You now have the data table, value labels and numeric codes in Stata.
preserve
contract patient_type_id patient_type
levels patient_type_id, local(ids)
levels patient_type, local(types)
local value_labels = ""
local n : word count `ids' // Adapted from the FAQ by Kevin Crow
forvalues i = 1/`n' {
local a : word `i' of `ids'
local b : word `i' of `types'
local b = char(34) + "`b'" + char(34)
// I'd be interested in improvements to that
local c `a' `b'
local value_labels `value_labels' `c'
}
restore
label define PatientTypes `value_labels'
// You now have Access's value labels as Stata value labels, with numeric
// codes preserved.
label values patient_type_id PatientTypes
sort patient_id
slist in 1/10, noobs
slist in 1/10, label noobs
exit
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
