topic Re: Help: All variables in a single column in SAS Programming

Help: All variables in a single column

Birgithj — Fri, 20 Sep 2019 08:58:56 GMT

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!

Re: Help: All variables in a single column

PeterClemmensen — Fri, 20 Sep 2019 09:08:10 GMT

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;

Re: Help: All variables in a single column

Birgithj — Fri, 20 Sep 2019 10:06:40 GMT

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 😕

Re: Help: All variables in a single column

PeterClemmensen — Fri, 20 Sep 2019 10:13:33 GMT

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

Re: Help: All variables in a single column

Birgithj — Fri, 20 Sep 2019 10:19:59 GMT

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.

Re: Help: All variables in a single column

Birgithj — Fri, 20 Sep 2019 10:42:14 GMT

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;

Re: Help: All variables in a single column

PeterClemmensen — Fri, 20 Sep 2019 11:03:54 GMT

Seems like the variable you named Returns is actually Return.

Anyway, glad you found your answer 🙂