Transpose data

ivanpersie — Sun, 08 Jul 2018 03:04:54 GMT

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

Re: Transpose data

FreelanceReinh — Sun, 08 Jul 2018 12:03:17 GMT

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;

topic Transpose data in SAS Programming

Transpose data

Re: Transpose data