Problem with a cumulative sum

[Onizuka]

Hello,

I have a problem with my cumulative some per group.

I don't understand why sometimes it works perfectly, and all of a sudden, it's completly messes and then it work again...

This is my programm ...

Proc sort data = mca.res_periode_niv2 ; by cniv1_cniv4 periode ; run ;

data mca.res_periode_niv2 ;
set mca.res_periode_niv2 ;
if periode = . then delete ;
by cniv1_cniv4 ;
if first.cniv1_cniv4 then sum_cum = 0 ;
sum_cum + cpt4 ;
run ;

And here you can see a picture when it's working :

You can see for example on line 13, there is a new cniv1_cniv4 and the sum start to 0 again but ...

On line 1610, you can see that SAS don't understand that it is a new group and he continues to sum...

Any ideas  ? ..

[ballardw]

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.

The example data set should be one that has the issue.

I am not, and doubt anyone else will type in that much data to attempt to run code to fix the problem.

Look at your input data set for the value of PERIODE for the first value of your by variable. If it is missing then you deleted the row with the first value and so the first. is NOT true for the following record.

Here is an example you can run that will duplicate that behavior:

data example;
   do i=1 to 3;
      do j = 1 to 4;
         if j=1 and i=2 then value=.;
         else value=rand('uniform');
         output;
      end;
   end;
run;

data result;
   set example;
   by i;
   if value=. then delete;
   Flag= first.i;
run;

Depending on WHY you are deleting records with missing peirode then moving the delete statement after the "If first. " might work.

[Onizuka]

Hello @ballardw 

Thank you very much for your answer and for your example which permit me to understand the problem I had.

Normally I put code on the topic (Data Have) but I thought that the error should be a syntax error, sorry for that..

It is doing the same problem if you put the delete after the first.i.

Using the solution of @Astounding  it is working, putting a "where periode ne . "

Thanks to you two ! 🙂

[Astounding · Accepted solution]

By using a DELETE statement, you sometimes delete an observation where your BY variable is 1. Instead, use:

where periode ne .;