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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.