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!!!
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;
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;
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?
I think I realized, by omitting the var statement the code includes all of the variables
I think you're correct, but all I can suggest is give it a try, and post back with any problems.
Tom
I actually tried it and it worked, thanks again!
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;
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 !
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.