st: RE: Generating a value depending on filename

[Joe Canner <jcanner1@jhmi.edu>]

Jack,

How about:

foreach initials in AK AL AR AZ CA {
  import excel `initials'_labor_vars.xls, sheet("BLS Data Series") firstrow
  gen stateinit = "`initials'"
  encode stateinit, gen(statecode)
}

The variable -stateinit- will be a string variable with the state's initials and the variable -statecode- will be a number from 1 to 51 corresponding to the alphabetical order of the initials.  Let us know if that is not what you want.

Regards,
Joe Canner
Johns Hopkins University School of Medicine

-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Jack Newsham
Sent: Tuesday, December 03, 2013 4:24 PM
To: statalist@hsphsun2.harvard.edu
Subject: st: Generating a value depending on filename

Hi all,

I'm working with state-by-state employment data on 51 Excel
spreadsheets that I plan to eventually append together and merge into
my main project file. Each spreadsheet is organized in columns
containing the year, month, and several measures of employment, with
only the name of the spreadsheet identifying the state whose data it
contains. I am attempting to create two variables -- "time" and
"state" -- to merge these data into my main file.

I'm somewhat of a Stata naif, however, and I'm running into a problem
when I try to generate "state" and give it a different value in every
spreadsheet. My use of  "if filename ==" and "if using" seem to be the
source of the problem, but I can't imagine how to generate a state
variable with a different value in each file. I've excerpted from my
do file, in relevant part. Can you advise? Is there a way to do this
in a loop? My thanks for any pointers.

foreach initials in AK AL AR AZ CA {
* The clear command is at the end of this loop.
import excel `initials'_labor_vars.xls, sheet("BLS Data Series") firstrow
* Create time variable by which to merge:
encode period,gen(monthnum)
gen time = (year * 100) + monthnum
* Create state variable by which to merge:
gen state = 01 if filename == "AL_labor_vars.xls" | 02 if filename ==
"AK_labor_vars.xls" |  ///
04 if filename == "AZ_labor_vars.xls" | 05 if filename ==
"AR_labor_vars.xls" | ///
06 if filename == "CA_labor_vars.xls"
}

Best,
Jack Newsham
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/