Setting up an unbalanced Panel Dataset in RATS

[nybammer]

Dear All,

I have the following daily dataset (318 609 obs) in Stata's dta format and from which I am including a sample:

PERMNO Date Date2 SIC1 SIC2 Price Val_Weight_Ret_incl_div Val_Weight_Ret_excl_div Eq_Weight_Ret_incl_div Eq_Weight_Ret_excl_div S_P_500_Ret
10145 19950103 1/3/95 3724 3714 34 -0.002393 -0.002426 0.004019 0.00399 -0.000348
10145 19950104 1/4/95 3724 3714 33.875 0.003319 0.003086 0.004864 0.00479 0.003485
10145 19950105 1/5/95 3724 3714 34.25 -0.000107 -0.000194 0.00306 0.003035 -0.000803
10145 19950106 1/6/95 3724 3714 34.375 0.00181 0.001804 0.00457 0.004551 0.000739
10145 19950109 1/9/95 3724 3714 34.125 0.001053 0.000885 0.002696 0.002473 0.000326
10145 19950110 1/10/95 3724 3714 34.75 0.002064 0.002035 0.001391 0.001341 0.001844
10145 19950111 1/11/95 3724 3714 35 -0.000447 -0.00046 0.00052 0.000503 -0.000022
10145 19950112 1/12/95 3724 3714 35.125 0.000179 0.000177 0.003075 0.003064 -0.000065
10145 19950113 1/13/95 3724 3714 35.5 0.008543 0.008378 0.00769 0.007606 0.00938
10145 19950116 1/16/95 3724 3714 36 0.007831 0.007831 0.004495 0.004494 0.007318
10145 19950117 1/17/95 3724 3714 36.125 0.002274 0.00223 0.003545 0.003505 0.001427
10145 19950118 1/18/95 3724 3714 36.125 -0.001145 -0.001156 0.000922 0.000892 -0.000723
10145 19950119 1/19/95 3724 3714 36.125 -0.005347 -0.005374 -0.002178 -0.002194 -0.005876
10145 19950120 1/20/95 3724 3714 36.125 -0.004911 -0.004915 -0.000352 -0.000364 -0.004647
10145 19950123 1/23/95 3724 3714 36 -0.000379 -0.000445 -0.004954 -0.004986 0.002238
10145 19950124 1/24/95 3724 3714 36.75 0.001973 0.001908 0.003531 0.003509 0.000086
10145 19950125 1/25/95 3724 3714 36.5 0.002194 0.002019 0.000672 0.000535 0.003392
10145 19950126 1/26/95 3724 3714 36 0.000558 0.000488 0.000818 0.000745 0.001883
10145 19950127 1/27/95 3724 3714 35.625 0.003729 0.003712 0.002577 0.002561 0.00442
10145 19950130 1/30/95 3724 3714 35.75 -0.004428 -0.004589 -0.004205 -0.004296 -0.003997
10145 19950131 1/31/95 3724 3714 35.75 0.003961 0.003749 0.005332 0.005247 0.004077
10145 19950201 2/1/95 3724 3714 35.375 0.001844 0.001788 0.001533 0.001486 -0.000043
10145 19950202 2/2/95 3724 3714 35.75 0.00462 0.004489 0.004336 0.004288 0.005081
10145 19950203 2/3/95 3724 3714 37.25 0.012267 0.012207 0.007553 0.007526 0.012395
10145 19950206 2/6/95 3724 3714 37.875 0.006088 0.005691 0.004546 0.004418 0.005202
10145 19950207 2/7/95 3724 3714 37.625 -0.000432 -0.000515 0.002004 0.001941 -0.000686
10145 19950208 2/8/95 3724 3714 38 0.001454 0.001361 0.002784 0.002623 0.00079
13856 20130614 6/14/13 2086 2086 82.13 -0.005371 -0.005385 -0.004604 -0.004649 -0.005885
13856 20130617 6/17/13 2086 2086 82.55 0.007177 0.007168 0.005131 0.005119 0.007567
13856 20130618 6/18/13 2086 2086 82.57 0.007355 0.007314 0.006078 0.00599 0.007791
13856 20130619 6/19/13 2086 2086 80.89 -0.013406 -0.013426 -0.009639 -0.009747 -0.013851
13856 20130620 6/20/13 2086 2086 78.91 -0.025743 -0.025844 -0.023687 -0.023715 -0.02501
13856 20130621 6/21/13 2086 2086 80.13 0.001442 0.001353 0.002747 0.002466 0.00267
13856 20130624 6/24/13 2086 2086 80.13 -0.012324 -0.012423 -0.014795 -0.014937 -0.012145
13856 20130625 6/25/13 2086 2086 80.04 0.010326 0.010238 0.009985 0.009877 0.009497
13856 20130626 6/26/13 2086 2086 81.3 0.008921 0.00862 0.005205 0.004851 0.00959
13856 20130627 6/27/13 2086 2086 81.74 0.008115 0.007911 0.011708 0.011424 0.0062
13856 20130628 6/28/13 2086 2086 81.79 -0.002796 -0.002842 0.001627 0.001533 -0.00429
13856 20130701 7/1/13 2086 2086 82.04 0.006723 0.006619 0.009141 0.009039 0.005404
13856 20130702 7/2/13 2086 2086 81.68 -0.001399 -0.00157 -0.002919 -0.002982 -0.000545
13856 20130703 7/3/13 2086 2086 80.73 0.000415 0.000405 -0.0001 -0.00013 0.000824
13856 20130705 7/5/13 2086 2086 80.8 0.009014 0.008977 0.005947 0.005934 0.010202
13856 20130708 7/8/13 2086 2086 81.78 0.005172 0.004973 0.003698 0.003661 0.005252
13856 20130709 7/9/13 2086 2086 82.77 0.007715 0.007692 0.00576 0.005704 0.00723
13856 20130710 7/10/13 2086 2086 82.99 0.000643 0.000571 0.00277 0.002691 0.000182
13856 20130711 7/11/13 2086 2086 84.55 0.014621 0.014479 0.012997 0.012768 0.013554
13856 20130712 7/12/13 2086 2086 84.32 0.002473 0.002466 0.001028 0.001018 0.003087
13856 20130715 7/15/13 2086 2086 84.72 0.002323 0.002311 0.005546 0.00549 0.001375
13856 20130716 7/16/13 2086 2086 84.02 -0.003775 -0.003779 -0.001546 -0.001572 -0.003709
13856 20130717 7/17/13 2086 2086 85.24 0.002997 0.002918 0.003696 0.003633 0.002774
13856 20130718 7/18/13 2086 2086 86.8 0.00545 0.005376 0.00497 0.004945 0.005033
13856 20130719 7/19/13 2086 2086 86.41 0.00148 0.001454 0.000552 0.000538 0.00161
13856 20130722 7/22/13 2086 2086 86.4 0.002796 0.002774 0.004195 0.004138 0.002033

I am trying to read the data into RATS using the following code:

Code: Select all

open data data_wrds1_upd1_test.dta
data(format=dta) 1 53 permno date date2 sic1 sic2 price Val_Weight_Ret_incl_div Val_Weight_Ret_excl_div $
                  Eq_Weight_Ret_incl_div Eq_Weight_Ret_excl_div S_P_500_Ret

pform(indiv=permno,time=date) p_vwrid
# Val_Weight_Ret_incl_div

For some reason I am not able to have RATS read in the data using either the smaller or the full sample. Sometimes I get an error memory message, sometimes the processing data window stays on but nothing seems to happen. Does anyone have an idea what is going on?

thank you.

[TomDoan]

Try leaving DATE2 out of the read list.

[nybammer]

I tried but it seems that nothing is changing. I'm afraid this is not the issue. Not sure what else could be.

Thanks for the super fast reply though.

[TomDoan]

Attach a sample-sized data set and I'll take a look.

[nybammer]

Here is the smaller dataset that I was trying to import:

https://www.dropbox.com/s/d3iv3qn4l49r ... .dta?dl=0

and here is the full one:

https://www.dropbox.com/s/qxtzdniodzeo5 ... 1.dta?dl=0

Hope the link works!

[TomDoan]

Well, I can't read it with RATS, but then, this is what I get from Stata:

file C:\TEMP\data_wrds1_upd1.dta not Stata format

I have no idea what format that file actually is.

[nybammer]

Ok, I am trying to link the original Excel files then:

Original: https://www.dropbox.com/s/1dhov7jv4e7n5 ... .xlsx?dl=0

and the smaller one: https://www.dropbox.com/s/gx4ndve3l5f0j ... .xlsx?dl=0

Hope these work.

[TomDoan]

The full sized XLSX file can't be read using Standard RATS because of its size. (While it zips down to 25Mb, the unzipped contents are 150Mb which expands to even more than that when the XML content are organized). It can be read using RATS Pro which has greater capacity.

BTW, the "STATA" file that you posted seems to be an XML dump of a .dta file. If you can get the original .dta file, it should work fine. (.DTA is more structured and so doesn't need as much memory to process as .XLSX).

[nybammer]

I think I figured out what was wrong. I was using Stata 13 to save the dataset into the .dta format. However, it seems that RATS only reads in the Stata 12 format (the older format). I saved the original file in this older format and everything seems to work like a charm.

Thanks a lot for helping me figure it out...

[nybammer]

Dear Tom,

I have a follow-up question on this issue. I am able to import and setup the data in RATS using the following code below:

Code: Select all

open data data_wrds1_upd2.dta
data(format=dta) / permno date year sic1 sic2 price Val_Weight_Ret_incl_div Val_Weight_Ret_excl_div $
                  Eq_Weight_Ret_incl_div Eq_Weight_Ret_excl_div S_P_500_Ret

pform(indiv=permno,time=date) p_year
# year
pform(indiv=permno,time=date) p_sic1
# sic1
pform(indiv=permno,time=date) p_sic2
# sic2
pform(indiv=permno,time=date) p_price
# price
pform(indiv=permno,time=date) p_vwrid
# Val_Weight_Ret_incl_div
pform(indiv=permno,time=date) p_vwred
# Val_Weight_Ret_excl_div
pform(indiv=permno,time=date) p_ewrid
# Eq_Weight_Ret_incl_div
pform(indiv=permno,time=date) p_ewred
# Eq_Weight_Ret_excl_div
pform(indiv=permno,time=date) p_sp500
# S_P_500_Ret

cal(panel=%nobs)
all %ngroup//%panelobs()

compute nindiv  = %panelsize(); dis nindiv
compute ntime   = %panelobs(); dis ntime
[b]print / date p_price p_year[/b]

Here is the link to the dataset:

Code: Select all

https://www.dropbox.com/s/xu1865pvn8ueok4/data_wrds1_upd2.dta?dl=0

I am mostly interested in making sure that each unit is properly indexed across time. However, beginning with unit 11 around year 2000 (20001018 to be precise) there is a mismatch between the date and p_year, respectively. What am I doing wrong?

Many thanks!

[TomDoan]

The "date" series is in the original data set sequencing order, while p_year and p_price are reblocked. If you create a series p_date from date with the same PFORM options as the others, you'll see that they all align properly.

[nybammer]

Yes, it is exactly like that.

Thank you.