Multiplicative Acumulation

[Junyong]

Suppose a stock return panel data set as follows.

data RAW;
format FIRM $8. DATE yymmddn8.;
do FIRM="GOOGL","MSFT";
do DATE="12apr2019"d to "22apr2019"d;
RETURN=round(0.01+sqrt(0.01)*rannor(1),0.01);
output;
end;
end;
run;

In many cases, I create a cumulative return by (1) using a log return, (2) accumulating, and (3) taking an exponential. I tried to shorten these tasks using IFN, but failed. Is there any shorter way to do the multiplicative works?

data PROCESSED;
set RAW;
by FIRM DATE;

/*1. Conventional. Works nicely, but too many lines*/
if first.FIRM then LOG=log(1+RETURN);
else LOG+log(1+RETURN);
CUMULATIVE=exp(LOG)-1;

/*2. Tried IFN to shorten, but doesn't work*/
LOG1=ifn(first.FIRM,log(1+RETURN),LOG1+log(1+RETURN));

/*3. Wondered if it's about LAG, but wasn't*/
LOG2=ifn(first.FIRM,log(1+RETURN),lag(LOG2)+log(1+RETURN));

/*4. Tried to accumulate by multiplying, but doesn't work*/
if first.FIRM then CUMULATIVE1=RETURN;
else CUMULATIVE1=(1+CUMULATIVE1)*(1+RETURN)-1;

/*5. Wondered if it's about LAG, but wasn't*/
if first.FIRM then CUMULATIVE2=RETURN;
else CUMULATIVE2=(1+lag(CUMULATIVE2))*(1+RETURN)-1;

/*6. Tried to shorten using IFN, but doesn't work*/
CUMULATIVE3=ifn(first.FIRM,RETURN,(1+CUMULATIVE3)*(1+RETURN)-1);

/*7. Tried the same thing with a LAG function, but doesn't work*/
CUMULATIVE4=ifn(first.FIRM,RETURN,(1+lag(CUMULATIVE4))*(1+RETURN)-1);
run;

Thanks.

[ballardw]

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[Junyong]

My apologies for missing the tables. The first code creates the raw data set and the second one is the outcomes I intend. Here I attach the log.

The first code.

1    data RAW;
2    format FIRM $8. DATE yymmddn8.;
3    do FIRM="GOOGL","MSFT";
4    do DATE="12apr2019"d to "22apr2019"d;
5    RETURN=round(0.01+sqrt(0.01)*rannor(1),0.01);
6    output;
7    end;
8    end;
9    run;

NOTE: The data set WORK.RAW has 22 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

The second code.

12   data PROCESSED;
13   set RAW;
14   by FIRM DATE;
15
16   /*1. Conventional. Works nicely, but too many lines*/
17   if first.FIRM then LOG=log(1+RETURN);
18   else LOG+log(1+RETURN);
19   CUMULATIVE=exp(LOG)-1;
20
21   /*2. Tried IFN to shorten, but doesn't work*/
22   LOG1=ifn(first.FIRM,log(1+RETURN),LOG1+log(1+RETURN));
23
24   /*3. Wondered if it's about LAG, but wasn't*/
25   LOG2=ifn(first.FIRM,log(1+RETURN),lag(LOG2)+log(1+RETURN));
26
27   /*4. Tried to accumulate by multiplying, but doesn't work*/
28   if first.FIRM then CUMULATIVE1=RETURN;
29   else CUMULATIVE1=(1+CUMULATIVE1)*(1+RETURN)-1;
30
31   /*5. Wondered if it's about LAG, but wasn't*/
32   if first.FIRM then CUMULATIVE2=RETURN;
33   else CUMULATIVE2=(1+lag(CUMULATIVE2))*(1+RETURN)-1;
34
35   /*6. Tried to shorten using IFN, but doesn't work*/
36   CUMULATIVE3=ifn(first.FIRM,RETURN,(1+CUMULATIVE3)*(1+RETURN)-1);
37
38   /*7. Tried the same thing with a LAG function, but doesn't work*/
39   CUMULATIVE4=ifn(first.FIRM,RETURN,(1+lag(CUMULATIVE4))*(1+RETURN)-1);
40   run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      22 at 22:39   22 at 25:44   20 at 29:20   20 at 33:20   22 at 36:37   22 at 36:50   22 at 36:61   22 at 39:37
      22 at 39:55   22 at 39:66
NOTE: There were 22 observations read from the data set WORK.RAW.
NOTE: The data set WORK.PROCESSED has 22 observations and 11 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds

Sorry again.

[Reeza]

Do you have SAS ETS licensed? Have you tried PROC EXPAND?

[Astounding]

#1 is very short.  Why is it too many lines?

If you are attempting to speed up the execution, get rid of one LOG function.  For example:

if first.firm then do;
   increment = log(1 + RETURN);
   log = increment;
   retain increment;
end;
else log + increment;
CUMULATIVE = exp(LOG) - 1;

I'm not sure whether the math is correct or not, but it is equivalent (generates the same result as #1).

[FreelanceReinh · Accepted solution]

Your approach #2 would benefit from a RETAIN statement:

retain log1;