"Transposing" Rows and Columns

[ilikesas]

Hi,

suppose I have the following table consisting of dates and stock prices:

Date	A	B
2014-01-07	10	20
2014-01-06	8	15

Is it possible to transform it into the following form so that I can calculate the return:

stock	date	price
A	2014-01-07	10
A	2014-01-06	8
B	2014-01-07	20
B	2014-01-06	15

Thank you!!!

[TomKari · Accepted solution]

PROC TRANSPOSE is ideal for jobs like this!

proc sort;

by date;

proc transpose out=want(rename=(_name_=stock col1=price));

var a b;

by date;

run;

[ilikesas]

Hi Tom, thanks for the reply!

But in my real case I have hundreds of stocks, so is it possible to put them into the var statement in a manner other than typing each one of them?

[ilikesas]

I think I realized, by omitting the var statement the code includes all of the variables

[TomKari]

I think you're correct, but all I can suggest is give it a try, and post back with any problems.

Tom

[ilikesas]

I actually tried it and it worked, thanks again!

[stat_sas]

data want(drop=i a b);

retain stock date price;

set have;

array var{*} a b;

do i=1 to dim(var);

price=var{i};

stock=vname(var{i});

output;

end;

run;

proc sort data=want;

by stock desending date;

run;

[ilikesas]

Hi stat@sas, thanks for the reply!

Just have a small issue and it is that in my real data I have hundreds of stocks so it will be very helpful if there was another method of putting them all in the code instead of typing each one.

Thanks !

[Karthikeyan]

Hi,

You can group all the numeric or character variables in a data set to an array , without typing each one of them.

array stock{*}  _numeric_ ;

Just replace the array statement in the code by stat@sas . But Make sure  other than the stock variables, the remaining should be chars . If they're not char already , make them chars.

Thanks

Karthik