Bookmark and Share

Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


From   Kieran McCaul <[email protected]>
To   "[email protected]" <[email protected]>
Subject   RE: st: RE: sxpose -not possible; would exceed present limit on number of variables
Date   Thu, 20 Feb 2014 11:09:46 +0800

...

Like this?

clear *

input  EntityID   pd   corpid   begyr   gvkey   endyr
   100091    1     8101    1961    1000    1970
   100091    2     8091    1971    1000    1973
   100091    3     8011    1974    1001    2000
   100091    4     8012    2000    1001    2002
   100091    5     8012    2003    1001    2005
end

gen diff = endyr - begyr + 1
expand diff
sort EntityID pd
by EntityID pd: gen year = begyr[1] + (_n - 1)

list, sepby(EntityID pd)




-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of R Zhang
Sent: Thursday, 20 February 2014 10:02 AM
To: [email protected]
Subject: Re: st: RE: sxpose -not possible; would exceed present limit on number of variables

Kieran,

Your interpretation of my data is correct. I was able to generate this table below with my real data. thanks a lot!

in my original post, I have a second question: construct a WANT table as follows from the reshaped results


     +-----------------Reshaped data-------------------+
     | EntityID   pd   corpid   begyr   gvkey   endyr |
     |------------------------------------------------|
  1. |   100091    1     8101    1961    1000    1970 |
  2. |   100091    2     8091    1971    1000    1973 |
  3. |   100091    3     8011    1974    1001    2000 |
  4. |   100091    4     8012    2000    1001    2002 |
  5. |   100091    5     8012    2003    1001    2005 |
     +------------------------------------------------+

I need to merge this data with another data set (call it financial) that is indexed by gvkey and year, how should I change the reshaped data to facilitate such a merge?

my thoughts (this may not be efficient) is to create another data like the one below

  +-----------------New data created from Reshaped data-------------------+

 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
+---------------------------------------------------------------------------------------+

1. how could I go from Reshaped data to New data ?

2. if you think I should not create New data in such a format, please also let me know.

+-----------financial data --------------+
   gvkey       year          assets
   1000          1960       200
   1000          1965      300
   1000          1966      3200
    1001          2003      3021
...........
+-----------financial data --------------+

thanks again,

R

On Wed, Feb 19, 2014 at 8:08 PM, Kieran McCaul <[email protected]> wrote:
> ....
>
> I have no idea what you data looks like NOW.
> The number of variables is the number of columns of data in your dataset.
> If your data consists of 21 variables, as you say it does, then it should be able to reshape it easily (see below).
>
> If this doesn’t work, then your data is not as you are describing it.
>
>
> clear *
>
> input 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 end
>
> reshape long corpid begyr gvkey endyr, i(EntityID) j(pd)
>
>
> list
>
>      +------------------------------------------------+
>      | EntityID   pd   corpid   begyr   gvkey   endyr |
>      |------------------------------------------------|
>   1. |   100091    1     8101    1961    1000    1970 |
>   2. |   100091    2     8091    1971    1000    1973 |
>   3. |   100091    3     8011    1974    1001    2000 |
>   4. |   100091    4     8012    2000    1001    2002 |
>   5. |   100091    5     8012    2003    1001    2005 |
>      +------------------------------------------------+
>
>
>
>
>
>
> -----Original Message-----
> From: [email protected] 
> [mailto:[email protected]] On Behalf Of R Zhang
> Sent: Thursday, 20 February 2014 7:31 AM
> To: [email protected]
> Subject: Re: st: RE: sxpose -not possible; would exceed present limit 
> on number of variables
>
> Kieran,
>
> the data I posted is just one observation with 21 variable to show what it looks like and what I want after transformation.  the actual data  13,458 observation and 21 variables. I apologize for the confusion.
>
> -Rochelle
>
>
> On Wed, Feb 19, 2014 at 4:20 PM, Kieran McCaul <[email protected]> wrote:
>> ...
>>
>>
>> The data you have looks like the following, is that correct?
>>
>> 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
>>
>> This dataset has 21 observations and 2 variables.
>>
>> You say your data has 21 variables and 13,458 observations.  If it is in the form above, then your data had 21 observations and 13,458 variables.
>>
>> Your version of Stata has a limit to how many variables you can read in:  You are exceeding it.
>>
>>
>>
>>
>>
>>
>>
>> -----Original Message-----
>> From: [email protected]
>> [mailto:[email protected]] On Behalf Of R Zhang
>> Sent: Thursday, 20 February 2014 5:06 AM
>> To: [email protected]
>> Subject: st: sxpose -not possible; would exceed present limit on 
>> number of variables
>>
>> 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.
>>
>> thanks!!!
>>
>> -R
>>
>> *
>> *   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/

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


© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index