Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

particular cumulative sum

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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 xy
a14
a13
a02
a12
a11
b02
b12
b11
b00
b00

Accepted Solutions
Solution
‎02-27-2012 09:26 PM
Respected Advisor
Posts: 4,649

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

View solution in original post


All Replies
PROC Star
Posts: 7,363

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;

Respected Advisor
Posts: 3,124

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
Respected Advisor
Posts: 4,649

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+

Respected Advisor
Posts: 4,649

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
Respected Advisor
Posts: 3,124

particular cumulative sum

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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 1352 views
  • 7 likes
  • 5 in conversation