BookmarkSubscribeRSS Feed
Anna_Guo
Calcite | Level 5

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

3 REPLIES 3
AncaTilea
Pyrite | Level 9

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;

Anna_Guo
Calcite | Level 5

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?

Linlin
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 705 views
  • 3 likes
  • 3 in conversation