topic Re: Cumulative Sum in SAS Programming

Cumulative Sum

RDellaVilla — Fri, 12 Nov 2021 18:39:12 GMT

Hi,

I have some issues to compute some Cumulative Sum. I have a dataset organized with some variables, and I have grouped all the different results according to three different variables (in the following order): PERMNO, EVTDATE, DATE.

I need to compute the Cumulative Sum for another variable of interest. i was thinking to use an if first. to do all my computations, setting it equal to the variable itself for the first value and then cumulate it.

Still, this does not function.

May I ask someone to help me with this, or if any suggestion?

Thanks

Re: Cumulative Sum

Reeza — Fri, 12 Nov 2021 19:00:06 GMT

Approach seems correct, please post the code so we can help you out.

Re: Cumulative Sum

ballardw — Fri, 12 Nov 2021 21:12:37 GMT

"Does not function" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" 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-data-AKA-generate/ta-p/258712 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 "</>" icon or attached as text to show exactly what you have and that we can test code against.

Re: Cumulative Sum

RDellaVilla — Sat, 13 Nov 2021 14:51:56 GMT

Thanks, I here paste the Code.

"data dataset_car;
set dataset_abret;
by permno evtdate date;
retain car;
if first.permno or (first.permno and first.evtdate) then
car = abret;
else car = car+abret;
run;"

Thing is, I have multiple evtdate and date per permno, so I need to cumulate my results based on this sequence: PERMNO,EVTDATE,DATE.

If you could help, that would be much appreciated!

Re: Cumulative Sum

PaigeMiller — Sat, 13 Nov 2021 14:59:13 GMT

The specific request was for you to provide the LOG, which includes the code plus any ERRORs, WARNINGs and NOTEs.

And we also would need to see a portion of the data, and incorrect output, if that is the problem. But really, you have not yet explained what the problem is ... explain, please.

Re: Cumulative Sum

RDellaVilla — Sat, 13 Nov 2021 15:51:28 GMT

So as for the Log, no mistake is shown/nor warning message: "

427        *Cumulative Abnormal Returns and Gross Return per PERMNO;
 428        
 429        data dataset_car;
 430        set dataset_abret;
 431        by permno evtdate date;
 432        retain car;
 433        if first.permno or (first.permno and first.evtdate) then
 434        car = abret;
 435        else car=car+abret;
 436        run;
 
 NOTE: There were 171237 observations read from the data set WORK.DATASET_ABRET.
 NOTE: The data set WORK.DATASET_CAR has 171237 observations and 12 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.03 seconds
       cpu time            0.04 seconds

Following the procedure to make the output compatible with the forum, here is attached a much filtered version of the output. Looking at the highlighted values, it seems that my procedure functions for the very first value associated with the Permno, but it does not change with the second layer (in bold): first value associated to the new Event Date. It keeps cumulating the results according to permno and does not stop at the new Event Date.

Permno EVTDate    Date       Abret     Cumulative Abret 
10051 24JUL2020 23JUL2020 -0.001997522 -0.001997522
10051 24JUL2020 24JUL2020 -0.008036756 -0.010034278
10051 24JUL2020 27JUL2020 -0.010701593 -0.020735871
[....] here is all the data up to the end of the time window around the Evtd Date 
10051 24JUL2020 04SEP2020 -0.016240639 0.0344095798
10051 27JUL2020 23JUL2020 -0.001997522 0.032412058
10051 27JUL2020 24JUL2020 -0.008036756 0.0243753022
[...] same  here, I have deleted all the intermediate results up to the end of the 2nd event date.
10051 27JUL2020 08SEP2020 -0.008131352 0.0606878076
10051 31JUL2020 23JUL2020 -0.001997522 0.0586902858

Re: Cumulative Sum

Tom — Sat, 13 Nov 2021 16:08:04 GMT

First thing is that if it is FIRST.PERMNO it is by definition also FIRST.EVTDATE because you have nested EVTDATE inside PERMNO. So your test for a new group is just going to be:

if first.evtdate then car = abret;

Make sure that CAR is not already on the input dataset. If it is then the SET statement will overwrite any value carried forward by the retaining the variable.

You could use a different variable name for your new variable. Or use the DROP= dataset option to remove the existing variable CAR. Or use RENAME= to change its name to something else.

So try something like this:

data dataset_car;
  set dataset_abret(drop=car);
  by permno evtdate date;
  car+abret;
  if first.evtdate then car = abret;
run;

Re: Cumulative Sum

Kurt_Bremser — Sat, 13 Nov 2021 16:12:05 GMT

Only use first.date. A change in a variable to the left of date in the BY (higher up in the hierarchy) will also cause a change for date, even when that value is the same.

data dataset_car;
set dataset_abret;
by permno evtdate date;
if first.date 
then car = abret;
else car + abret;
run;

The SUM Statement will cause an automatic RETAIN.