topic Re: How do I create a cumulative total column subset by another variable? in SAS Procedures

How do I create a cumulative total column subset by another variable?

PQK — Sun, 24 Jun 2012 03:42:43 GMT

Dear Forum,

Although conceptually easy to grasp, I have yet been able to devise a working piece of code that will create a cumulative total column that resets at each break in another variable. For example, given the following sample data set.

ID AMT

10 150

10 100

25 150

30 600

30 300

The result I would like to achieve is a cumulative total column for each distinct ID value (as follows):

ID AMT SumbyID

10 150 150

10 100 250

25 150 150

25 150 300

25 150 450

30 600 600

30 300 900

Thus far I have attempted various sum statement and do loop combinations using the last.<variable> as a marker, however I would be interest to know if their is a better method using PROC SQL.

Thank you in advance for your help.

Sincerely,

PQK

Re: How do I create a cumulative total column subset by another variable?

Linlin — Sun, 24 Jun 2012 04:33:30 GMT

how about:

data have;

input ID AMT;

cards;

10 150

10 100

25 150

30 600

30 300

;

data want;

set have;

by id notsorted;

if first.id then sumbyid=0;

sumbyid+amt;

run;

proc print;run;

Obs ID AMT sumbyid

1 10 150 150

2 10 100 250

3 25 150 150

4 25 150 300

5 25 150 450

6 30 600 600

7 30 300 900

Linlin

Re: How do I create a cumulative total column subset by another variable?

PQK — Mon, 25 Jun 2012 23:34:24 GMT

Linlin,

As I suspected, there was an easier way that I completely overlooked. Thank you for the tip!

I forgot to mention that I would need to create a cumulative total column subset by more than one variable. It slipped my mind at the time, but fortunately I resolved the problem by taking the two subsetting variables and concatenating them together to create a new temporary variable. After that, is was just a matter of using the same code example you suggested above.

Thanks again.

PQK

Re: How do I create a cumulative total column subset by another variable?

MikeZdeb — Tue, 26 Jun 2012 02:14:29 GMT

hi ... if I understand what you mean by a "cumulative total column subset by more than one variable" you can try this too ...

data x;

input a b c;

datalines;

1 1 10

1 2 20

1 3 30

2 1 100

2 2 50

2 2 60

;

run;

data y;

set x;

by a b;

cumab + (-first.b * cumab) + c;

run;

a b c cumab

1 1 10 10

1 1 10 20

1 1 10 30

1 2 20 20

1 3 30 30

1 3 30 60

2 1 100 100

2 1 100 200

2 2 50 50

2 2 60 110

Re: How do I create a cumulative total column subset by another variable?

jimbobob — Fri, 05 Sep 2014 21:57:23 GMT

Mike awesome code can you explain how this is interpreted by SAS:

cumab + (-first.b * cumab) + c;

Re: How do I create a cumulative total column subset by another variable?

MikeZdeb — Mon, 08 Sep 2014 20:56:57 GMT

hi ... I added the value of FIRST.B to the output to help the explanation ...

Obs a b c cumab first_b

1 1 1 10 10 1

2 1 1 10 20 0

3 1 1 10 30 0

4 1 2 20 20 1

5 1 3 30 30 1

6 1 3 30 60 0

7 2 1 100 100 1

8 2 1 100 200 0

9 2 2 50 50 1

10 2 2 60 110 0

values of the variable C are accumulated within values of the two variables A and B

when then first observation is read, the value of CUMAB is zero since that variable is an accumulator variable in a SUM statement ... so for the first observation, in this statement

cumab + (-first.b * cumab) + c

the value within the parentheses is zero and it just adds the value of variable C to CUMAB ... the value of FIRST.B is zero for all the remaining calculations within a group, so the value of the term within parentheses is once again zero ... you are just adding C to CUMAB

when you arrive at the first observation for the next group, the value of FIRST.B is 1 ... you add C to CUMAB, but you also subtract the LAST value of CUMAB from the total, so you are starting at zero again for the next group

that make sense ?

Re: How do I create a cumulative total column subset by another variable?

jimbobob — Mon, 08 Sep 2014 21:23:09 GMT

Thanks Mike for explanation

Re: How do I create a cumulative total column subset by another variable?

sin_golbarg — Sun, 28 Sep 2014 20:03:56 GMT

Hi linlin I think without this syntex it's working, so we don't need this syntax.

if first.id then sumbyid=0;

Re: How do I create a cumulative total column subset by another variable?

retheesh_analytic_gmail_com — Thu, 06 Nov 2014 10:23:24 GMT

Hi,

I have a similar scenario

data x;

input a b c;

datalines;

1 1 10

2 1 10

3 1 10

4 2 20

5 3 30

6 3 30

7 1 100

8 1 100

9 2 50

;

run;

I want the result as follows

a b c cumab

1 1 10 10

2 1 10 20

3 1 10 30

4 2 20 20

5 3 30 30

6 3 30 60

7 1 100 100

8 1 20 120

9 2 50 50

The column "a" keeps the same order and I want the sum of the column "c" based on the values in the column "b".

Could you help my to calculate the column"cumab" as in the above table?

Thanks and Regards,

Retheesh Ravi.

Re: How do I create a cumulative total column subset by another variable?

Ksharp — Thu, 06 Nov 2014 12:32:52 GMT


data x;
input a b c;
datalines;
1 1 10
2 1 10
3 1 10
4 2 20
5 3 30
6 3 30
7 1 100
8 1 100
9 2 50
;
run;
data want;
 set x;
 by b notsorted;
 if first.b then cumab=0;
 cumab+c;
run;

Xia Keshan

Re: How do I create a cumulative total column subset by another variable?

MikeZdeb — Mon, 10 Nov 2014 17:50:02 GMT

hi ... another way (same result) ...

data y;

set x;

by b notsorted;

cumab + (-first.b * cumab) + c;

run;

Re: How do I create a cumulative total column subset by another variable?

BLarson — Wed, 16 Sep 2015 18:11:00 GMT

Thank you for the examples. I am a fairly new SAS user and this was very timely. Is it possible to take this one step further, where the cumulative total is a running total of the previous 5 observations, all under the same subsets? Thank you.

Re: How do I create a cumulative total column subset by another variable?

vijaylaxmi — Sun, 17 Apr 2016 02:25:46 GMT

I am very new to SAS. Base SAS we can do with datastep. But how can we do it with Proc SQL??

Re: How do I create a cumulative total column subset by another variable?

Lamia — Thu, 06 Apr 2017 12:34:23 GMT

Good Day every one,

I am new with SAS and i am having a problem in calculating the cumulative sum of values, my table looks like this one

I used this code to calculate the cumulative sum in column C, by respect to id

data Ratings1;
Set Ratings;
retain c;
if first.id then c=0;
by id notsorted;
c+j1;
run;

the problem i have is that it gives me as result the same values as in column j1 as if the addition is not working.

Can you help me please , where is the problem in this code

thank you in advance

Re: How do I create a cumulative total column subset by another variable?

vijaylaxmi — Thu, 06 Apr 2017 14:32:49 GMT

Hi Lamia,

This is quite interestting. To me it looks like as C variable is in orignal data set, so code that you have written is not working. Everytime row is getting read of rating dataset C is getting assigned 0. Here is how we can get around it.

data ratings1;
set ratings(drop=c);
retain c;
if first.id then c=0;
put first.id last.id;
by id notsorted;
c+j1;
run;

Re: How do I create a cumulative total column subset by another variable?

Lamia — Fri, 07 Apr 2017 02:48:21 GMT

thank you very much, the code worked perfectly, can you explain me please what is the use of drop c for the original dataset, and retain for the output one?

also why this instuction: put first.id last.id; ?

thank you

Re: How do I create a cumulative total column subset by another variable?

vijaylaxmi — Fri, 07 Apr 2017 13:01:48 GMT

Using Drop C is the main trick here. What is does is it will drop that column when creating dataset.

Try this

Data ratings1;

set ratings(drop = C);

run;

Ratings1 will have all variable apart from C.

Retain C. What this statement does it,

1 it will not reinitialize value of C to missing on reading new observation,

2 remember value from previous obsevration.

Try reading some sas paper for more on retain statement, it pretty powerful statement.

put first.id last.id;

well this statement is not needed, I was just trying out something. You can remove this statement and program will work just fine. Main purpose of put is in debugging, it will write/print values in log for reach observation.

Re: How do I create a cumulative total column subset by another variable?

Lamia — Fri, 07 Apr 2017 13:58:41 GMT

thanks alot, it helps alot