BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ilikesas
Barite | Level 11

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ilikesas
Barite | Level 11

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;

View solution in original post

8 REPLIES 8
Reeza
Super User

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/

ilikesas
Barite | Level 11

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!

Reeza
Super User

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. 

ilikesas
Barite | Level 11

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

Reeza
Super User

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. 

ilikesas
Barite | Level 11

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;

Reeza
Super User

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;

ilikesas
Barite | Level 11

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1434 views
  • 4 likes
  • 2 in conversation