particular cumulative sum

[jesusmontreal]

Feeling quiet rusty so far with sums, could somebody help me to understand how to do this in a data step?

"Y" is a particular cumulative sum; it represents the sum of values in the subsequent obervations in x (for each group a and b).

ID	x	y
a	1	4
a	1	3
a	0	2
a	1	2
a	1	1
b	0	2
b	1	2
b	1	1
b	0	0
b	0	0

[art297]

Not sure exactly what you are asking but, given the data you presented, here is one way to achieve it:

data have;

  input ID $ x;

  n=_n_;

  cards;

a          1

a          1

a          0

a          1

a          1

b          0

b          1

b          1

b          0

b          0

;

proc sort data=have;

  by descending n;

run;

data want;

  set have;

  by descending id;

  if first.id then y=0;

  y+(x eq 1);

run;

proc sort data=want;

  by n;

run;

[Haikuo]

Another alternative;

data want;

  do until(last.id);

    set have;

      by id;

      y+x;

    end;

    do until (last.id);

       set have;

       by id;

       output;  

       y=y-x;

     end;

run;

Regards,

Haikuo

[PGStats · Accepted solution]

data have;
input ID $ x;
datalines;
a 1
a 1
a 0
a 1
a 1
b 0
b 1
b 1
b 0
b 0
;

data have1;
set have;
obsNo = _n_;
run;

proc sort data=have1; by id descending obsNo; run;

data want;
set have1;
by id;
if first.id then y=0;
y+x;
run;

proc sort data=want out=want(drop=obsNo); by id obsNo; run;

proc print; run;

That does it. - PG

[jesusmontreal]

Thanks for your input guys !

I will test them tomorrow and keep you posted !

A+

[PGStats]

And just for the sake of ecumenism :smileygrin: :

data have;
input ID $ x;
datalines;
a 1
a 1
a 0
a 1
a 1
b 0
b 1
b 1
b 0
b 0
;

data have1;
set have;
obsNo = _n_;
run;

proc sql;
create table want as
select A.ID, A.x, sum(B.x) as y
from have1 as A, have1 as B
where A.id=B.id and A.obsNo<=B.obsNo
group by A.id, A.obsNo, A.x
order by A.id, A.obsNo;

drop table have1;

proc print data=want; run;

[Haikuo]

Very good! Thanks a lot! And I had to look up " ecumenism".

[Linlin]

data have;

  input ID $ x;

  n=_n_;

  cards;

a          1

a          1

a          0

a          1

a          1

b          0

b          1

b          1

b          0

b          0

;

proc sort data=have;

  by id descending n;

run;

data want(drop=n);

  set have;

  by id;

  y+(-first.id*y)+x;

run;

proc sort data=want;

  by id descending y x;

run;

proc print ;run;

                             Obs    ID    x    y

                                  1    a     1    4

                                  2    a     1    3

                                  3    a     0    2

                                  4    a     1    2

                                  5    a     1    1

                                  6    b     0    2

                                  7    b     1    2

                                  8    b     1    1

                                  9    b     0    0

                                 10    b     0    0

Linin

[jesusmontreal]

Hi all,

I just wanted to thank you again.

I used the code from PGstats. It gave me exactly what I was looking for on the first attempt.

I tried a little bit the others, they we pretty close (and very interesting) for sure if I would have had more time I could have made them work as well !

Bonne soiree !!!