Re: st: sxpose -not possible; would exceed present limit on number of variables

[Carlos Avellaneda Suárez <carlos.avellaneda8@gmail.com>]

Hi Rochelle,
You forgot to run the second part of Nick's excellent code:
drop v1
reshape wide v2, i(EntityID j) j(which) string
renpfix v2
expand endyr - begyr + 1
rename begyr year
bysort EntityID j : replace year = year[_n-1] + 1 if _n > 1
drop endyr j
l

2014-02-20 2:59 GMT-05:00 Nick Cox <njcoxstata@gmail.com>:
> Sorry, but I don't understand the question. The data you show clearly
> includes -gvkey- and -year-, the variables you say you want.
>
> Nick
> njcoxstata@gmail.com
>
> On 20 February 2014 03:08, R Zhang <r05zhang@gmail.com> wrote:
>
>> thanks again.
>>
>> running your code, I got
>> +++++++++++++++++++++
>> v1 v2 j which EntityID
>> corpid1 8101 1 corpid 100091
>> begyr1 1961 1 begyr 100091
>> gvkey1 1000 1 gvkey 100091
>> endyr1 1970 1 endyr 100091
>> corpid2 8091 2 corpid 100091
>> begyr2 1971 2 begyr 100091
>> gvkey2 1000 2 gvkey 100091
>> endyr2 1973 2 endyr 100091
>> corpid3 8011 3 corpid 100091
>> begyr3 1974 3 begyr 100091
>> gvkey3 1001 3 gvkey 100091
>> endyr3 2000 3 endyr 100091
>> corpid4 8011 4 corpid 100091
>> begyr4 1974 4 begyr 100091
>> gvkey4 1001 4 gvkey 100091
>> endyr4 2000 4 endyr 100091
>> corpid5 8011 5 corpid 100091
>> begyr5 1974 5 begyr 100091
>> gvkey5 1001 5 gvkey 100091
>> endyr5 2000 5 endyr 100091
>> +++++++++++++++++++++
>>
>> from the above output, is it easy to get
>>
>>  +------------------------------------+
>>
>>  EntityID    corpid  year gvkey
>>
>> 100091   8101    1961 1000
>> 100091   8101    1962 1000
>> ...
>> 100091   8101    1970 1000
>> 100091   8091   1971 1000
>> 100091   8091   1972 1000
>> 100091   8091   1973 1000
>> .....
>> 100091     8012  2003 1001
>> +-------------------------------------------+
>>
>> I would like the corresponding gvkey and year so I can match merge
>> with another dataset using thse two variables.
>>
>> -Rochelle
>>
>> On Wed, Feb 19, 2014 at 8:47 PM, Nick Cox <njcoxstata@gmail.com> wrote:
>>> I am not using -sxpose- (SSC) or the -reshape- following. The key
>>> point is that that approach will not work for you, as already
>>> established. My code starts after the sample data is read in by
>>>
>>> input str20 v1 v2
>>> EntityID 100091
>>> corpid1 8101
>>> begyr1 1961
>>> gvkey1 1000
>>> endyr1 1970
>>> corpid2 8091
>>> begyr2 1971
>>> gvkey2 1000
>>> endyr2 1973
>>> corpid3 8011
>>> begyr3 1974
>>> gvkey3 1001
>>> endyr3 2000
>>> corpid4 8012
>>> begyr4 2001
>>> gvkey4 1001
>>> endyr4 2002
>>> corpid5 8013
>>> begyr5 2003
>>> gvkey5 1001
>>> endyr5 2004
>>> end
>>>
>>> Nick
>>> njcoxstata@gmail.com
>>>
>>>
>>> On 20 February 2014 01:43, R Zhang <r05zhang@gmail.com> wrote:
>>>> Hi, Nick
>>>>
>>>> I fixed the 3, 4, 5 repeated information . I ran your Simplified code
>>>> and got an error " v1 not found", please let me know if  I
>>>> misunderstood your email.
>>>>
>>>> ***********************
>>>> clear
>>>> input str20 v1 v2
>>>> EntityID 100091
>>>> corpid1 8101
>>>> begyr1 1961
>>>> gvkey1 1000
>>>> endyr1 1970
>>>> corpid2 8091
>>>> begyr2 1971
>>>> gvkey2 1000
>>>> endyr2 1973
>>>> corpid3 8011
>>>> begyr3 1974
>>>> gvkey3 1001
>>>> endyr3 2000
>>>> corpid4 8012
>>>> begyr4 2001
>>>> gvkey4 1001
>>>> endyr4 2002
>>>> corpid5 8013
>>>> begyr5 2003
>>>> gvkey5 1001
>>>> endyr5 2004
>>>> end
>>>>
>>>> compress
>>>> sxpose, clear firstnames force
>>>> reshape long corpid begyr gvkey endyr, i(EntityID) j(pd)
>>>>
>>>> gen j = substr(v1, -1, 1) if v1 != "EntityID"
>>>> gen which = subinstr(v1, j, "", 1) if v1 != "EntityID"
>>>> gen EntityID = v2 if v1 == "EntityID"
>>>> replace EntityID = EntityID[_n-1] if missing(EntityID)
>>>> drop if v1 == "EntityID"
>>>>
>>>> ***********************
>>>>
>>>> thanks again,
>>>>
>>>> On Wed, Feb 19, 2014 at 7:50 PM, Nick Cox <njcoxstata@gmail.com> wrote:
>>>>> Simplified code:
>>>>>
>>>>> gen j = substr(v1, -1, 1) if v1 != "EntityID"
>>>>> gen which = subinstr(v1, j, "", 1) if v1 != "EntityID"
>>>>> gen EntityID = v2 if v1 == "EntityID"
>>>>> replace EntityID = EntityID[_n-1] if missing(EntityID)
>>>>> drop if v1 == "EntityID"
>>>>> drop v1
>>>>> reshape wide v2, i(EntityID j) j(which) string
>>>>> renpfix v2
>>>>> expand endyr - begyr + 1
>>>>> rename begyr year
>>>>> bysort EntityID j : replace year = year[_n-1] + 1 if _n > 1
>>>>> drop endyr j
>>>>> l
>>>>>
>>>>> Nick
>>>>> njcoxstata@gmail.com
>>>>>
>>>>>
>>>>> On 20 February 2014 00:34, Nick Cox <njcoxstata@gmail.com> wrote:
>>>>>> In your sample data, blocks *3 *4 *5 seem to be the same information repeated.
>>>>>>
>>>>>> With  the sample data, this is code to play with
>>>>>>
>>>>>> gen j = substr(word(v1, 1), -1, 1) if word(v1, 1) != "EntityID"
>>>>>> gen which = subinstr(v1, j, "", 1) if word(v1, 1) != "EntityID"
>>>>>> gen EntityID = v2 if word(v1, 1) == "EntityID"
>>>>>> replace EntityID = EntityID[_n-1] if missing(EntityID)
>>>>>> drop if word(v1,1) == "EntityID"
>>>>>> drop v1
>>>>>> reshape wide v2, i(EntityID j) j(which) string
>>>>>> renpfix v2
>>>>>> expand endyr - begyr + 1
>>>>>> rename begyr year
>>>>>> bysort EntityID j : replace year = year[_n-1] + 1 if _n > 1
>>>>>> drop endyr j
>>>>>> l
>>>>>> Nick
>>>>>> njcoxstata@gmail.com
>>>>>>
>>>>>>
>>>>>> On 19 February 2014 21:06, R Zhang <r05zhang@gmail.com> wrote:
>>>>>>> Hi Statalisters,
>>>>>>>
>>>>>>>
>>>>>>> My data has 13,458 observation and 21 variables.
>>>>>>> EntityID corpid1 begyr1 gvkey1 endyr1 corpid2 begyr2 gvkey2 endyr2
>>>>>>> corpid3 begyr3 gvkey3 endyr3 corpid4 begyr4 gvkey4 endyr4 corpid5
>>>>>>> begyr5 gvkey5 endyr5
>>>>>>> 100091 8101 1961 1000 1970 8091 1971 1000 1973 8011 1974 1001 2000
>>>>>>> 8012 2000 1001 2002 8012 2003 1001 2005
>>>>>>>
>>>>>>>
>>>>>>> for each unique EntityID, the corresponding gvkey and corpid could
>>>>>>> vary over time as indicated by begyr and endyr,
>>>>>>>
>>>>>>> what I want is a dataset that give me the gvkey and corpid for each
>>>>>>> time period, so I can match it to another dataset that has company
>>>>>>> specific financial data , the match variable will be gvkey, year.
>>>>>>>
>>>>>>> as of now, i thought I should reshape the data, Someone on the forum
>>>>>>> kindly offered me the following program to reshape my data. sample
>>>>>>> code (see below) works for his hypothetical data, but when i ran with
>>>>>>> my data (13,458 observation and 21 variables.). I got an error "not
>>>>>>> possible; would exceed present limit on number of variables", could
>>>>>>> you shed light on this?
>>>>>>>
>>>>>>> *****************
>>>>>>> input str20 v1 v2
>>>>>>> EntityID 100091
>>>>>>> corpid1 8101
>>>>>>> begyr1 1961
>>>>>>> gvkey1 1000
>>>>>>> endyr1 1970
>>>>>>> corpid2 8091
>>>>>>> begyr2 1971
>>>>>>> gvkey2 1000
>>>>>>> endyr2 1973
>>>>>>> corpid3 8011
>>>>>>> begyr3 1974
>>>>>>> gvkey3 1001
>>>>>>> endyr3 2000
>>>>>>> corpid4 8011
>>>>>>> begyr4 1974
>>>>>>> gvkey4 1001
>>>>>>> endyr4 2000
>>>>>>> corpid5 8011
>>>>>>> begyr5 1974
>>>>>>> gvkey5 1001
>>>>>>> endyr5 2000
>>>>>>> end
>>>>>>>
>>>>>>> compress
>>>>>>> sxpose, clear firstnames force
>>>>>>> reshape long corpid begyr gvkey endyr, i(EntityID) j(pd)
>>>>>>> ***********************
>>>>>>>
>>>>>>> what I ultimately want is :
>>>>>>> EntityID corpid year gvkey
>>>>>>> 100091 8101 1961 1000
>>>>>>> 100091 8101 1962 1000
>>>>>>> 100091 8101 1963 1000
>>>>>>> 100091 8101 1964 1000
>>>>>>> 100091 8101 1965 1000
>>>>>>> 100091 8101 1966 1000
>>>>>>> ...
>>>>>>> 100091 8091 1971 1000
>>>>>>> 100091 8091 1972 1000
>>>>>>> 100091 8091 1973 1000
>>>>>>> 100091 8091 1974 1000
>>>>>>>
>>>>>>> p.s if you think there is a better way , please also share.
> *
> *   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/