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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.