topic Re: particular cumulative sum in SAS Data Science

particular cumulative sum

jesusmontreal — Tue, 28 Feb 2012 02:08:57 GMT

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

particular cumulative sum

art297 — Tue, 28 Feb 2012 02:20:20 GMT

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 0

a 1

b 0

b 1

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;

particular cumulative sum

Haikuo — Tue, 28 Feb 2012 02:26:18 GMT

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

particular cumulative sum

PGStats — Tue, 28 Feb 2012 02:26:41 GMT

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

particular cumulative sum

jesusmontreal — Tue, 28 Feb 2012 02:44:53 GMT

Thanks for your input guys !

I will test them tomorrow and keep you posted !

A+

particular cumulative sum

PGStats — Tue, 28 Feb 2012 13:15:26 GMT

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;

particular cumulative sum

Haikuo — Tue, 28 Feb 2012 13:20:39 GMT

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

Re: particular cumulative sum

Linlin — Tue, 28 Feb 2012 14:41:06 GMT

data have;

input ID $ x;

n=_n_;

cards;

a 1

a 0

a 1

b 0

b 1

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

particular cumulative sum

jesusmontreal — Thu, 01 Mar 2012 01:28:10 GMT

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