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

[joon1]

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;

[ballardw]

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.

[joon1]

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

[LeonidBatkhan]

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,

[Tom]

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;

[joon1]

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.

[joon1 · Accepted solution]

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;