Help: All variables in a single column

[Birgithj]

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!

[PeterClemmensen · Accepted solution]

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;

[Birgithj]

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 😕

[PeterClemmensen]

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

[Birgithj]

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.

[Birgithj]

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;

[PeterClemmensen]

Seems like the variable you named Returns is actually Return.

Anyway, glad you found your answer 🙂