## performing an operation to a list of variables inside a table

Solved
Super Contributor
Posts: 459

# performing an operation to a list of variables inside a table

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!

Accepted Solutions
Solution
‎11-14-2016 11:31 PM
Super Contributor
Posts: 459

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

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;

All Replies
Super User
Posts: 24,010

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

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/

Super Contributor
Posts: 459

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

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!

Super User
Posts: 24,010

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

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.

Super Contributor
Posts: 459

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

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

Super User
Posts: 24,010

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

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.

Super Contributor
Posts: 459

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

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;

Super User
Posts: 24,010

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

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;

Solution
‎11-14-2016 11:31 PM
Super Contributor
Posts: 459

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

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;

☑ This topic is solved.