Best technique to convert to panel data

Question

I have some returns data on 1000+ firms that I want to convert into panel form.

From my understanding, it is neither truly wide nor long form (at least from the examples I've seen).

I have attached an example of the original data set and what I want it to look like. Is there a way to achieve this? I am intermediate with Excel/VBA, and new to SAS/Stata but can use them and self-teach myself.

This is exactly wide data and a basic Proc transpose is all that's needed in SAS. If you can't determine the code post what you've tried. Hint: only specify your input and output dataset and by date variable. — Reeza, Sep 28 '16 at 12:39
Good questions here show _some_ attempt at code and (in this territory) data examples that can copied and pasted. — Nick Cox, Sep 28 '16 at 16:32

score 3 · Answer 1 · edited Sep 28 '16 at 16:42

This can be done very easily with proc transpose in SAS. All you will need to add is a column name for column A. This will be your by variable so that the following variables will be transposed along each specific date. Other than that just make sure your data is sorted by the date column. The code would look similar to this:

proc sort data=have;
by date;
run;

proc transpose data=have out=want; /* you could add a name= or prefix= statement here to rename your variables */
by date;
run;

score 3 · Accepted Answer · answered Sep 28 '16 at 15:51

Consider this example using reshape in Stata:

clear *
input float(date FIRM_A FIRM_B FIRM_C FIRM_D)
 1 .14304407   .8583148  .3699433  .7310092
 2 .34405795   .9531917  .6376472  .2895169
 3 .04766626   .6588161  .6988417  .5564945
 4 .21615694  .18380463  .4781089  .3058527
 5   .709911     .85116 .14080866 .10687433
 6  .3805699 .070911616 .55129284  .8039169
 7  .1680727   .7267236  .1779183 .51454383
 8  .3610604   .1578059 .15383714  .9001798
 9  .7081585   .9755411 .28951603 .20034006
10 .27780765   .8351805 .04982195  .3929535
end

reshape long FIRM_, i(date) j(Firm_ID) string
rename FIRM_ return
replace Firm_ID = "Firm " + Firm_ID

list in 1/8, sepby(date)
     +---------------------------+
     | date   Firm_ID     return |
     |---------------------------|
  1. |    1    Firm A   .1430441 |
  2. |    1    Firm B   .8583148 |
  3. |    1    Firm C   .3699433 |
  4. |    1    Firm D   .7310092 |
     |---------------------------|
  5. |    2    Firm A   .3440579 |
  6. |    2    Firm B   .9531917 |
  7. |    2    Firm C   .6376472 |
  8. |    2    Firm D   .2895169 |
     +---------------------------+

see help reshape for more on the topic.

Thanks for your response. This looks so close to what I need, but actually the output I am after needs to have all the obersvations for Firm A in the order of dates and corresponding returns ascending. How would I alter it to get that? — elbarto, Sep 29 '16 at 05:28
There is still a problem with this. I am not sure how to sort by ascending date and ascending id at the same time. That is, have the date ascending for id = 1, then looping back to the start and ascending for id = 2. I tried `gsort +date +id` to no avail — elbarto, Sep 29 '16 at 08:35
Scratch that - it actually worked but it's sorted the dates into ascending order, but recognizing the dates variable as string... i just need to change it to date — elbarto, Sep 29 '16 at 08:42

Best technique to convert to panel data

2 Answers2