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 |
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;
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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.