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 |
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;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.