st: Re: ST: transferring labels

Eric Uslaner wrote:
I have downloaded a data set that is in 3 parts, all in Excel. Files 2
and 3 are data, file 1 is a list of variable names and labels. I have
transferred the data with names to Stata, but the labels are perplexing.
Here is the format of the Excel spreadsheet with the variable names
and labels:
Column A Column B Column C Column D Column E
varname varlabel divider varname varlabel
etc. etc. etc.
etc.
Columns A&B are for data set 2 and C & D for data set 3 (though this is
no problem--I can copy them into one column since the cases are
identical).
Is there a way to transfer the variable labels without doing a lot of
typing. Stata Technical Support suggested putting each variable label
into a do-file with quotation marks around each label (which I would
have to type in by hand) and then pasting:
label var varname "varlabel" for each variable.
Does anyone have a more expeditous (and less tedious) way of doing this
transfer?
-------------------------------------------------------------------------------
Yes. You can create the necessary do-files in Stata, using its string
functions, especially concatenation. Get the Excel spreadsheet into Stata via
a tab-delimited or comma-delimited text file, or via ODBC or Stat/Transfer.
Then insert the quotation marks around the variable labels. An ASCII quotation
mark is -char(34)-. Then use concatenation to create a new variable that
contains the command, append an "exit" (Stata likes "exit" at the end of its do-
files) and, finally, -outsheet- the column containing the concatentated string
command into a text do-file, without variable names and without quotation
marks. I've illustrated the technique below in a do-file. The first several
lines are just to create a set of variables that represent an Excel spreadsheet
patterned after the one you describe. (Careful--the e-mail message and
serverlist software might wrap the long datalines in the dummy spreadsheet data
below.)
Joseph Coveney
--------------------------------------------------------------------------------
*
* First few lines just to create dummy spreadsheet use for illustration
*
clear
set obs 3
input str20 ColumnA str25 ColumnB str20 ColumnC str20 ColumnD str25 ColumnE
"file2_var1" "file2_var1_label" "[spacer] " "file3_var1" "file3_var1_label"
"file2_var2" "file2_var2_label" "[spacer] " "file3_var2" "file3_var2_label"
"file2_var3" "file2_var3_label" "[spacer] " "file3_var3" "file3_var3_label"
// note that you don't need to use -end- when you've -set obs 3-, and
// have already entered three lines of data
*
* The technique begins here
*
replace ColumnB = char(34) + ColumnB + char(34)
replace ColumnE = char(34) + ColumnE + char(34)
generate str ColumnA1 = "label variable " + ColumnA + " " + ColumnB
generate str ColumnD1 = "label variable " + ColumnD + " " + ColumnE
count
set obs `=r(N) + 1'
replace ColumnA1 = "exit" in l
replace ColumnD1 = "exit" in l
outsheet ColumnA1 using a:/VariableLabelsforFile2.do, noquote nonames
outsheet ColumnD1 using a:/VariableLabelsforFile3.do, noquote nonames
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/