topic "Transposing" Rows and Columns in SAS Data Management

"Transposing" Rows and Columns

ilikesas — Sat, 04 Apr 2015 01:09:10 GMT

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!!!

Re: "Transposing" Rows and Columns

TomKari — Sat, 04 Apr 2015 01:33:43 GMT

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;

Re: "Transposing" Rows and Columns

ilikesas — Sat, 04 Apr 2015 01:41:56 GMT

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?

Re: "Transposing" Rows and Columns

stat_sas — Sat, 04 Apr 2015 02:14:57 GMT

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;

Re: "Transposing" Rows and Columns

ilikesas — Sat, 04 Apr 2015 02:31:45 GMT

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 !

Re: "Transposing" Rows and Columns

Karthikeyan — Sat, 04 Apr 2015 03:21:42 GMT

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

Re: "Transposing" Rows and Columns

ilikesas — Sat, 04 Apr 2015 03:22:48 GMT

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

Re: "Transposing" Rows and Columns

TomKari — Sat, 04 Apr 2015 03:31:04 GMT

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

Tom

Re: "Transposing" Rows and Columns

ilikesas — Sat, 04 Apr 2015 03:33:36 GMT

I actually tried it and it worked, thanks again!