SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

"Transposing" Rows and Columns

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

"Transposing" Rows and Columns

Hi,

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

DateAB
2014-01-071020
2014-01-06815

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

stockdateprice
A2014-01-0710
A2014-01-068
B2014-01-0720
B2014-01-0615

Thank you!!!


Accepted Solutions
Solution
‎04-03-2015 09:33 PM
PROC Star
Posts: 1,167

Re: "Transposing" Rows and Columns

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;

View solution in original post


All Replies
Solution
‎04-03-2015 09:33 PM
PROC Star
Posts: 1,167

Re: "Transposing" Rows and Columns

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;

Super Contributor
Posts: 441

Re: "Transposing" Rows and Columns

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?

Super Contributor
Posts: 441

Re: "Transposing" Rows and Columns

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

PROC Star
Posts: 1,167

Re: "Transposing" Rows and Columns

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

Tom

Super Contributor
Posts: 441

Re: "Transposing" Rows and Columns

I actually tried it and it worked, thanks again!

Trusted Advisor
Posts: 1,228

Re: "Transposing" Rows and Columns

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;

Super Contributor
Posts: 441

Re: "Transposing" Rows and Columns

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 !

Contributor
Posts: 37

Re: "Transposing" Rows and Columns

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 535 views
  • 3 likes
  • 4 in conversation