Transpose data

Reply
Contributor
Posts: 30

Transpose data

Hi guys. I have the following data (attached)  and i would like it to be stacked up in one column. Its monthly price data for different companies (represented by company codes). Apparently each company occupies its own column but i want all companies to be stacked up in one column and also align with the respective dates. How can i do this in SAS 9.4. i appreciate your help.

 

Sample data

 

Date VT:VIC(P) VT:VNM(P) VT:VCB(P) VT:GAS(P)
2014/12/1 28411.75 69096.69 20888.86 85000
2014/11/1 28762.52 72916.13 20888.86 105000
2014/10/1 28937.89 74305 19555.53 109000
2014/9/1 34199.33 77082.75 20740.71 124000
2014/8/1 29682.32 78124.06 19111.08 111000
2014/7/1 25357.74 70601 19259.23 111000
2014/6/1 26537.17 71758.44 18679.63 96500
2014/5/1 25554.31 80438.88 18550.8 99500
2014/4/1 28502.89 81596.25 20032.29 83000
Trusted Advisor
Posts: 1,252

Re: Transpose data

[ Edited ]
Posted in reply to ivanpersie

Hi @ivanpersie,

 

It's good that you've provided sample data. Sample data in the form of a data step would have been even more convenient for people who want to test their suggested solutions:

data have;
input date :yymmdd10. VIC VNM VCB GAS;
format date yymmdd10.;
cards;
2014/12/1 28411.75 69096.69 20888.86 85000
2014/11/1 28762.52 72916.13 20888.86 105000
2014/10/1 28937.89 74305 19555.53 109000
2014/9/1 34199.33 77082.75 20740.71 124000
2014/8/1 29682.32 78124.06 19111.08 111000
2014/7/1 25357.74 70601 19259.23 111000
2014/6/1 26537.17 71758.44 18679.63 96500
2014/5/1 25554.31 80438.88 18550.8 99500
2014/4/1 28502.89 81596.25 20032.29 83000
;

You can use PROC TRANSPOSE to obtain the transposed dataset:

proc transpose data=have out=want(rename=(_name_=company col1=price));
by date notsorted;
var VIC--GAS;
run;

With the above dataset HAVE the BY statement could read by descending date; as well. In any case there must be no duplicate dates. A data step solution would be more robust to such duplicates and it would give you more control over length and label of variable COMPANY:

data want;
set have;
length company $3;
array p VIC--GAS;
do i=1 to dim(p);
  company=vname(p[i]);
  price=p[i];
  output;
end;
keep date company price;
run;

 

Ask a Question
Discussion stats
  • 1 reply
  • 57 views
  • 0 likes
  • 2 in conversation