Hi,
I am trying to prepare data for a time-series analysis, but all my variables are stores in a single column. Currently my data looks something like this:
Date | Stock | Returns |
Jan/18 | Stock A | 1% |
Feb/18 | Stock A | 1% |
Mar/18 | Stock A | 1% |
Apr/18 | Stock A | 1% |
May/18 | Stock A | 1% |
Jan/18 | Stock B | 2% |
Feb/18 | Stock B | 2% |
Mar/18 | Stock B | 2% |
Apr/18 | Stock B | 2% |
May/18 | Stock B | 2% |
Instead I want each stock to have its own column like this:
Date | Stock A | Stock B |
Jan/18 | 1% | 2% |
Feb/18 | 1% | 2% |
Mar/18 | 1% | 2% |
Apr/18 | 1% | 2% |
May/18 | 1% | 2% |
Can someone please help?
Thanks!
Hi and welcome to the SAS Community 🙂
data have;
input Date :monyy6. Stock $ Returns :percent.;
format Date monyy6.;
infile datalines4 dlm=',';
datalines;
Jan/18,Stock A,1%
Feb/18,Stock A,1%
Mar/18,Stock A,1%
Apr/18,Stock A,1%
May/18,Stock A,1%
Jan/18,Stock B,2%
Feb/18,Stock B,2%
Mar/18,Stock B,2%
Apr/18,Stock B,2%
May/18,Stock B,2%
;
proc sort data=have;
by Date;
run;
proc transpose data=have out=want(drop=_:);
by Date;
id Stock;
var Returns;
run;
Hi and welcome to the SAS Community 🙂
data have;
input Date :monyy6. Stock $ Returns :percent.;
format Date monyy6.;
infile datalines4 dlm=',';
datalines;
Jan/18,Stock A,1%
Feb/18,Stock A,1%
Mar/18,Stock A,1%
Apr/18,Stock A,1%
May/18,Stock A,1%
Jan/18,Stock B,2%
Feb/18,Stock B,2%
Mar/18,Stock B,2%
Apr/18,Stock B,2%
May/18,Stock B,2%
;
proc sort data=have;
by Date;
run;
proc transpose data=have out=want(drop=_:);
by Date;
id Stock;
var Returns;
run;
Hi,
Thank you for your input. Unfortunately this does not solve my problem. All the stock returns are still in the same column with the only difference being, that the data is sorted by date instead of stock 😕
This is the result from my code.
Date Stock A Stock B JAN18 0.01 0.02 FEB18 0.01 0.02 MAR18 0.01 0.02 APR18 0.01 0.02 MAY18 0.01 0.02
Very weird.
My output looks like this even when running to code without any modifications.
1 | Jan/18 | Stock A | 00.01 |
2 | Jan/18 | Stock B | 00.02 |
3 | Feb/18 | Stock A | 00.01 |
4 | Feb/18 | Stock B | 00.02 |
5 | Mar/18 | Stock A | 00.01 |
6 | Mar/18 | Stock B | 00.02 |
7 | Apr/18 | Stock A | 00.01 |
8 | Apr/18 | Stock B | 00.02 |
9 | May/18 | Stock A | 00.01 |
10 | May/18 | Stock B | 00.02 |
But thanks anyways for trying to help me.
FYI,
With a small modification I was able to get the code to work! Thank you for leading me in the right direction 🙂 The code I used was
proc sort data=have;
by Date;
run;
proc transpose data=have out=new;
by date;
id stock;
var return;
run;
Seems like the variable you named Returns is actually Return.
Anyway, glad you found your answer 🙂
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.