topic Re: Do loop (macro) in constructing annual buy and hold return using monthly returns in SAS Programming

Do loop (macro) in constructing annual buy and hold return using monthly returns

joon1 — Thu, 02 Apr 2020 16:26:25 GMT

Dear Madam/Sir,

I try one example code to construct annual buy and hold return (returns for eleven months ahead of closing month plus return in closing month) using monthly return.

It will be grateful if anybody can advise me how to fix the following error in do loop. The data set is attached. Thank you. Joon1

data c4;
set c3;
by permno;
if _n_=1 then do until (end_of_annualreturn);
set annualbhr end=end_of_annualreturn;

ERROR: File WORK.ANNUALBHR.DATA does not exist.

array annualbhr {1990:2019,1:12} _temporary_ ;
array logplusone {12} _temporary_;
y=year(date);
m=month(date);
logplusone{m}=log(1+ret);
if closemonth^=.;
if n(of logplusone{*})=12 then annualbhr{y,m}=exp(sum(of logplusone{*}));
end;
run;

Re: Do loop (macro) in constructing annual buy and hold return using monthly returns

ballardw — Thu, 02 Apr 2020 16:56:19 GMT

It is pretty clear: your work library does not have a data set named ANNUALBHR.

Check spelling of the name, or if the set is in a different library use the <lib>.datasetname syntax.

If you created the data set in a previous SAS session then you likely need to recreate it as the Work library is cleared out at the end of a SAS session.

Re: Do loop (macro) in constructing annual buy and hold return using monthly returns

joon1 — Thu, 02 Apr 2020 20:29:00 GMT

Thanks for your prompt reply, ballardw. Annualbhr is not library name created in the previous step. It is the variable name that I want to construct as shown at the last line in the program.

I am not familiar with macro do-loop logic, so I posted this question in the forum.

I am not asking the interpretation of error. My question was how to construct do loop from month 1 (-11 month) to month 12 (closing month).

(1+ret)(1+ret)----(1+ret(closing month)-1=annualbhr

Any help will be appreciated.

Thanks

Joon1

Re: Do loop (macro) in constructing annual buy and hold return using monthly returns

LeonidBatkhan — Fri, 03 Apr 2020 01:46:51 GMT

Hi joon1,

When you write in your code

set annualbhr end=end_of_annualreturn;

SAS interprets that as you are reading data table named WORK.ANNUALBHR (statement SET is used for reading data tables). Since this data table does not exist you get that ERROR: File WORK.ANNUALBHR.DATA does not exist.

There is nothing wrong with your array named annualbhr.

Hope this helps.

Regards,

Re: Do loop (macro) in constructing annual buy and hold return using monthly returns

Tom — Fri, 03 Apr 2020 01:48:40 GMT

Looks like you probably just need to read until you see the observation with CLOSEMONTH non-missing.

data want ;
  do n=1 by 1 until(not missing(closemonth));
     logplusone=log(1+ret);
     sumlogplusone=sum(0,sumlogplusone,logplusone);
  end;
  if n=12 then annualbhr =exp(sumlogplusone));
  drop rev;
run;

Re: Do loop (macro) in constructing annual buy and hold return using monthly returns

joon1 — Fri, 03 Apr 2020 03:43:30 GMT

Thanks, Tom, for your suggesion.

However, your programming makes sas program running for a couple of hours without producing output. It seems that there is a indefinite loop in your code. What does "drop rev" indicate? Thanks, Joon1.

Re: Do loop (macro) in constructing annual buy and hold return using monthly returns

joon1 — Fri, 03 Apr 2020 05:58:54 GMT

Thank you very much to all.

The following code works well.

data c4;
set c3;
by permno;
do n=1 by 1 until(not missing(closemonth));
array logplusone {12} _temporary_;
y=year(date);
m=month(date);
logplusone{m}=log(1+ret);
if closemonth^=.;
if n(of logplusone{*})=12 then annualbhr=exp(sum(of logplusone{*}))-1;
end;
run;