Hi,
I have a table of historical stock prices, among other things, where each stock is in a column. I also have all the names of the stocks in a macro list called "symbol".
I would like to calculate the return for each stock. I have tried the following code:
%macro returns(all_data);
%let k=1;
%let symbol= %scan(&all_data, &k);
%do %while("&symbol" NE "");
&symbol&_&ret = log(&symbol) - log(lag(&symbol));
%let k = %eval(&k + 1);
%let symbol = %scan(&all_data, &k);
%end;
%mend;
data returns;
set returns;
%returns(&symbol);
run;
But the code doesn't work. I don't get any error message, but the new stock return columns don't appear.
Thanks!
Thanks Reeza for the guidance, so I will summarize for future readers.
1) I have a table of the names of the stocks:
data the_stocks;
input symbol$;
datalines;
msft
ko
ibm
duk
bp
;
run;
(And I also have a table of the stock returns where each stock price is in its own column)
2) Here create a list of the stocks and also a list of the stock returns in the form "stock_ret"
Proc SQL noprint;
select catx('_', symbol, 'ret') into :returns separated by ' '
from the_stocks ;
select symbol into :symbol separated by ' '
from the_stocks;
quit;
3) And finally calculate the stock returns by putting both lists into arrays:
data returns (drop= i);
set returns;
array returns{*} &returns;
array symbol{*} &symbol;
do i =1 to dim(returns);
returns[i] = log(symbol[i])- log(lag(symbol[i]));
end;
run;
Don't use a macro, use an array or transpose your data and use BY group processing.
I was actually trying to put a list into an array with the following code:
data returns (drop = i );
set returns;
array returns{*} &symbol;
do i =1 to dim(returns);
returns[i]_ret = log(returns[i])- log(lag(returns[i] ))
end;
run;
I know that I can write manually all the stock symbols into the array but I have too many stocks...
That is why here I tried to put the list into the array. I was not surprised when the code didn't work, but is it even possible?
Thank you!
You need two arrays. See the 'COMPUTING NEW VARIABLES' example in the link I provided.
Depending on how you created your variable list creating a second variable list with the suffix ret can be easy or hard.
But in this case I still need to manually enter all the names of the stocks?
Not unless you want to.
You said they were stored in STOCKS macro variable. How did you create that list? Modify to create a second list of variables that the required suffix and call it something like stocks_lag.
OK I think I got it:
1) first I added a new column to create a stock_ret:
data the_stocks;
set the_stocks;
returns = cats(symbol,"_ret");
run;
2) Then I created a list for the stock_ret:
proc sql noprint ;
select returns
into :returns separated by ' '
from the_stocks;
quit;
3) And then I used the arrays to calculate the stock returns:
data returns (drop= i);
set returns;
array returns{*} &returns;
array symbol{*} &symbol;
do i =1 to dim(returns);
returns[i] = log(symbol[i])- log(lag(symbol[i]));
end;
run;
Looks good!
Only suggestion is you can create list directly from stock list and add an order by to be dead certain the observations are in the same order.
Proc SQL noprint;
select catx('_', symbol, 'ret') into :returns separated by ' '
from the_stocks
order by symbol;
select symbol into :stocks separated by ' '
from the_stocks
order by symbol;
quit;
Thanks Reeza for the guidance, so I will summarize for future readers.
1) I have a table of the names of the stocks:
data the_stocks;
input symbol$;
datalines;
msft
ko
ibm
duk
bp
;
run;
(And I also have a table of the stock returns where each stock price is in its own column)
2) Here create a list of the stocks and also a list of the stock returns in the form "stock_ret"
Proc SQL noprint;
select catx('_', symbol, 'ret') into :returns separated by ' '
from the_stocks ;
select symbol into :symbol separated by ' '
from the_stocks;
quit;
3) And finally calculate the stock returns by putting both lists into arrays:
data returns (drop= i);
set returns;
array returns{*} &returns;
array symbol{*} &symbol;
do i =1 to dim(returns);
returns[i] = log(symbol[i])- log(lag(symbol[i]));
end;
run;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.