Re: st: Generating a value depending on filename

[Jeph Herrin <stata@spandrel.net>]

That should be

local files : dir . files "*_labor_vars.xls"

On 12/3/2013 8:53 PM, Jeph Herrin wrote:
> You can get the file list, and append at the same time:
>
>    local files : dir . files *_labor_vars.xls
>    clear
>    save states, emptyok replace
>    foreach F in `files' {
>      local state = substr("`F'",1,2)
>      import excel `F', sheet("BLS Data Series") firstrow
>      gen str2 state="`state'"
>      append using states
>      save states, replace
>   }
>
> If you want to add a FIPS state code later, just use a cross walk to
> match to the -state- variable.
>
> hope this helps,
> Jeph
>
>
> On 12/3/2013 4:24 PM, Jack Newsham wrote:
> > 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/