topic performing an operation to a list of variables inside a table in SAS Programming

performing an operation to a list of variables inside a table

ilikesas — Mon, 14 Nov 2016 03:23:39 GMT

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!

Re: performing an operation to a list of variables inside a table

Reeza — Mon, 14 Nov 2016 03:59:22 GMT

Don't use a macro, use an array or transpose your data and use BY group processing.

http://www.ats.ucla.edu/stat/sas/seminars/SAS_arrays/

Re: performing an operation to a list of variables inside a table

ilikesas — Mon, 14 Nov 2016 04:11:04 GMT

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!

Re: performing an operation to a list of variables inside a table

Reeza — Mon, 14 Nov 2016 04:34:43 GMT

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.

Re: performing an operation to a list of variables inside a table

ilikesas — Mon, 14 Nov 2016 04:54:30 GMT

But in this case I still need to manually enter all the names of the stocks?

Re: performing an operation to a list of variables inside a table

Reeza — Mon, 14 Nov 2016 05:01:59 GMT

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.

Re: performing an operation to a list of variables inside a table

ilikesas — Mon, 14 Nov 2016 06:01:50 GMT

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;

Re: performing an operation to a list of variables inside a table

Reeza — Mon, 14 Nov 2016 06:08:16 GMT

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;

Re: performing an operation to a list of variables inside a table

ilikesas — Tue, 15 Nov 2016 04:31:14 GMT

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;