RE: st: RE: Generating a value depending on filename

["Sarah Edgington" <sedging@ucla.edu>]

Jack,
Sergiy's solution is one option, but it seems to me like if you have a list
of state abbreviations and a list of FIPS codes your code might be easier to
read later if you use those directly rather than trying to recreate the fips
codes mathematically.  Depending on what your other data looks like, you may
also want to keep fips as a string for merging purposes (that is, you may
want to keep the leading zero for fips codes less than 10).

Here's an example using the five states you listed but if you have both the
states and the fips codes in tabular form it should be pretty easy to create
both macros for all 50 states.

local states "AK AL AR AZ CA"
local fips "01 02 04 05 06"

forv i=1/5 {
	local abbr : word `i' of `states'
	local num : word `i' of `fips'

	import excel `abbr'_labor_vars.xls, sheet("BLS Data Series")
firstrow

**if you want to be sure fips is a string var use this
	gen str2 state="`num'"
}


If you want a numeric fips variable that drops the leading zero you can
instead use:
	gen state=`num'


-Sarah

-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Sergiy Radyakin
Sent: Tuesday, December 03, 2013 2:04 PM
To: statalist@hsphsun2.harvard.edu
Subject: Re: st: RE: Generating a value depending on filename

local allstates "AK AL EZ AR AZ PZ CA"
local territories "EZ PZ"
foreach initials of local allstates  {
   if (strpos(`"`territories'"',`"`initials'"')) continue
   local statecode=1+floor(strpos(`"`allstates'"',`"`initials'"')/3)
   display "`initials' `statecode'"
}

On Tue, Dec 3, 2013 at 4:58 PM, Jack Newsham <j.newsham@gmail.com> wrote:
> Dear Joel,
>
> I think this comes close, but I see two problems:
>
> 1) the numerical "state" variable I'm attempting to create is designed 
> to match the FIPS state codes in my main project dataset from the 
> Census, and the FIPS system skips five numbers (3, 7, 14, 43, and 52) 
> for non-state US territories. Starting with AR, I'd have a lot of 
> numbers to replace.
>
> 2) The alphabetical order of state initials does not match the 
> alphabetical order of states themselves (starting with AriZona and 
> ARkansas), which is what FIPS is based on.
>
> Best,
> Jack Newsham
>
> On Tue, Dec 3, 2013 at 4:48 PM, Joe Canner <jcanner1@jhmi.edu> wrote:
>> 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/
> *
> *   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/

*
*   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/