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

[Sergiy Radyakin <serjradyakin@gmail.com>]

Jack, in addition to the previous posting, couple of more comments:

THIS:
gen state = 01 if filename == "AL_labor_vars.xls" | 02 if filename
=="AK_labor_vars.xls"
WILL NOT WORK as you intend:
1) filename will be interpreted as a variable name. You probably mean
`"`c(filename)'"'. But don't use it after importing data.

2) and 3) are actually so common I wrote a reusable text for beginners here:
http://radyakin.org/statalist/text/conditions.htm

4) if you loop by the initials, use them in branching (this is an
advice, not a bug) instead of the filename. Instead of
if filename == "AL_labor_vars.xls"
you can write
if `"`initials'"' == "AL"
(just in case you'll need to process e.g. prices data instead of the
labor indicators one day).

5) if you loop by a string content, consider generating a string
variable, then append all 50 datasets, then encode the string variable
to a numeric variable in one swoop.

6) there are tons of different ways to do things in Stata. Given Joe's
and my comments, perhaps restructure your question into two: one
idealistic: what do I want to achieve? (encode a variable to match a
certain coding, leaving gaps in codes, match FIPS, whatever) and
another implementational (how exactly do I do this and that?)

Best, Sergiy Radyakin


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/