topic Re: Change panel data format in SAS Procedures

Change panel data format

Anna_Guo — Mon, 14 Jan 2013 19:53:15 GMT

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

Year	Firm1 price	Firm1 volume	Year	Firm2 price	Firm2 volume	Year	Firm3 price	Firm3 volume
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

I would like to change them to following format:

Year	Firm	Price	Volume
1970	1	2	11
1971	1	3	2
1972	1	4	3
1973	1	2	44
1974	1	1	4
1975	1	23	99
1976	1	39	33
1970	2	8	7
1971	2	3	8
1972	2	3	6
1973	2	2	1
1974	2	8	5
1975	2	8	4
1976	2	8	8
1970	3	1	84
1971	3	0	3
1972	3	1	43
1973	3	0	12
1974	3	0	86
1975	3	1	58
1976	3	1	63

Re: Change panel data format

AncaTilea — Mon, 14 Jan 2013 20:10:53 GMT

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;

Re: Change panel data format

Anna_Guo — Mon, 14 Jan 2013 21:40:09 GMT

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?

Re: Change panel data format

Linlin — Mon, 14 Jan 2013 23:22:01 GMT

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