Help using Base SAS procedures

Change panel data format

Reply
Contributor
Posts: 36

Change panel data format

Can some one help me change the data format? I have follwing table ( my actual data would be much larger). Thanks!

YearFirm1 priceFirm1 volumeYearFirm2 priceFirm2 volumeYearFirm3 priceFirm3 volume
19702111970871970184
197132197138197103
1972431972361972143
19732441973211973012
1974141974851974086
197523991975841975158
197639331976881976163

I would like to change them to following format:

YearFirmPriceVolume
19701211
1971132
1972143
19731244
1974114
197512399
197613933
1970287
1971238
1972236
1973221
1974285
1975284
1976288
19703184
1971303
19723143
19733012
19743086
19753158
197631

63

Super Contributor
Posts: 543

Re: Change panel data format

Hi Anna,

how about this?;

data wide;

input Year    Firm1_price    Firm1_volume    Year    Firm2_price    Firm2_volume    Year    Firm3_price    Firm3_volume;

cards;

1970    2    11    1970    8    7    1970    1    84

1971    3    2    1971    3    8    1971    0    3

1972    4    3    1972    3    6    1972    1    43

1973    2    44    1973    2    1    1973    0    12

1974    1    4    1974    8    5    1974    0    86

1975    23    99    1975    8    4    1975    1    58

1976    39    33    1976    8    8    1976    1    63

;

run;

DATA long ;

  SET wide ;

 

  year = year;price = firm1_Price;volume = firm1_volume;firm = 1;

  OUTPUT ;

  year = year; price = firm2_Price;volume = firm2_volume;firm = 2;

  OUTPUT ;

  year = year; price = firm3_Price;volume = firm3_volume;firm = 3;

  OUTPUT ;

  DROP firm1_:  firm2_: firm3:_;

RUN;

proc sort data = long;by firm;run;

Contributor
Posts: 36

Re: Change panel data format

Anca,

Than you for your help.

I only include a simple sample with 3 firms. But I  have over firms, the code will be too long. Also, the firm name will not be firm1 firm2, they are actual firm names. Is there any easier way to do it?

Super Contributor
Posts: 1,636

Re: Change panel data format

borrowed Anca's data:

data wide;

input Year    Firm1_price    Firm1_volume    Year    Firm2_price    Firm2_volume    Year    Firm3_price    Firm3_volume;

cards;

1970    2    11    1970    8    7    1970    1    84

1971    3    2    1971    3    8    1971    0    3

1972    4    3    1972    3    6    1972    1    43

1973    2    44    1973    2    1    1973    0    12

1974    1    4    1974    8    5    1974    0    86

1975    23    99    1975    8    4    1975    1    58

1976    39    33    1976    8    8    1976    1    63

;

data long;

  set wide;

  array _price(*)  firm1_price firm2_price firm3_price;

  array _volume(*)  firm1_volume firm2_volume firm3_volume;

  do _n_=1 to dim(_price);

  price=_price(_n_);

  volume=_volume(_n_);

  firm=scan(vname(_price(_n_)),1,'_');

  output;

  end;

  keep year firm price volume;

  proc print;run;

                   Obs    Year    price    volume    firm

                        1    1970       2       11      Firm1

                        2    1970       8        7      Firm2

                        3    1970       1       84      Firm3

                        4    1971       3        2      Firm1

                        5    1971       3        8      Firm2

                        6    1971       0        3      Firm3

                        7    1972       4        3      Firm1

                        8    1972       3        6      Firm2

                        9    1972       1       43      Firm3

                       10    1973       2       44      Firm1

                       11    1973       2        1      Firm2

                       12    1973       0       12      Firm3

                       13    1974       1        4      Firm1

                       14    1974       8        5      Firm2

                       15    1974       0       86      Firm3

                       16    1975      23       99      Firm1

                       17    1975       8        4      Firm2

                       18    1975       1       58      Firm3

                       19    1976      39       33      Firm1

                       20    1976       8        8      Firm2

                       21    1976       1       63      Firm3

Ask a Question
Discussion stats
  • 3 replies
  • 153 views
  • 3 likes
  • 3 in conversation