Solved
New Contributor
Posts: 3

particular cumulative sum

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

Accepted Solutions
Solution
‎02-27-2012 09:26 PM
Posts: 4,919

particular cumulative sum

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

PG

All Replies
PROC Star
Posts: 7,468

particular cumulative sum

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;

Posts: 3,156

particular cumulative sum

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

Solution
‎02-27-2012 09:26 PM
Posts: 4,919

particular cumulative sum

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

PG
New Contributor
Posts: 3

particular cumulative sum

Thanks for your input guys !

I will test them tomorrow and keep you posted !

A+

Posts: 4,919

particular cumulative sum

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;

PG
Posts: 3,156

particular cumulative sum

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

Super Contributor
Posts: 1,636

Re: particular cumulative sum

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

New Contributor
Posts: 3

particular cumulative sum

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 !!!

🔒 This topic is solved and locked.