I got it. Here is the code I finally used.
clear
forval i=1/8 {
use Neighbour.dta
expand 44
bysort state: gen year=1964+_n
ren state state2
keep n`i' state2 year
ren n`i' state
sort state year
merge state year using TaxData.dta
tab _merge
drop if _merge!=3
drop _merge
ren state s`i'
ren tax n`i'
ren state2 state
sort state year
if (`i'==1){
save tempdata.dta, replace
}
else {
merge state year using tempdata.dta
drop _merge
sort state year
save tempdata.dta, replace
}
}
sort state year
merge state year using TaxData.dta
drop _merge
On Thu, May 28, 2009 at 11:32 AM, Rijo John <[email protected]> wrote:
> Hi Austin,
>
> thanks. Unfortunately this does not yield the result I am looking for.
> It creates as many rows as number of neighbors for each state. Since
> there is no observation in the final data set for those states that
> has no neighbors.
>
> I will keep trying on the lines you suggested anyways.
> Thanks,
>
> On Thu, May 28, 2009 at 10:47 AM, Austin Nichols
> <[email protected]> wrote:
>> Rijo John <[email protected]>:
>> One way-- open the second dataset, call it d2, expand by number of
>> years, then merge 8 times to get neighbors' tax rates, then once more
>> to put on own tax rates, like so:
>>
>> use d2
>> expand 44
>> bys Ste: g Year=1964+_n
>> ren Ste hold
>> forv i=1/8 {
>> ren n`i' Ste
>> joinby Ste Year using d1, unm(both)
>> ta _m
>> drop if _m==2
>> drop _m
>> ren Ste s`i'
>> ren tax n`i'
>> }
>> ren hold Ste
>> joinby Ste Year using d1, unm(both)
>>
>> [untested code, but the idea should be clear]
>>
>> On Thu, May 28, 2009 at 11:32 AM, Rijo John <[email protected]> wrote:
>>> Hello statalist,
>>>
>>> I have a data set as below.
>>>
>>> Ste fips Year tax
>>> AL 1 1965 6.3
>>> AK 2 1965 8.0
>>> AZ 4 1965 4.3
>>> AR 5 1965 7.7
>>> CA 6 1965 3.0
>>> CO 8 1965 4.2
>>>
>>> Its a panel data for all 51 states running from year 1965 through 2008.
>>>
>>> I want to generate few new variables from it. Basically I want to
>>> create one variable for each neighboring states as n1 n2 n3 and son
>>> on.I have another data that shows the neighborhood information as
>>> follows.
>>>
>>> Ste n1 n2 n3 n4 n5 n6 n7 n8
>>> AL FL GA MS TN
>>> AK NONE
>>> AZ CA CO NV NM UT
>>> AR LA MS MO OK TN
>>> CA AZ NV OR
>>> CO AZ KS NE NM OK UT WY
>>> CT MA NY RI
>>>
>>> Here, for the state AL the values for n1 n2 & n3 should be the values
>>> of "tax" in FL, GA, and TN respectively for the respective years.
>>> Similarly for AK all the n's will be empty. and for AZ, n1 to n5 will
>>> take values of the variable tax in CA, CO, NV. NM and UT respectively
>>> for the respective years.
>>> There is a maximum of only 8 neighbors for a state and minimum zero.
>>> Can someone advise me how I can proceed with it?
>>>
>>
>> *
>> * For searches and help try:
>> * http://www.stata.com/help.cgi?search
>> * http://www.stata.com/support/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/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
