turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Calculate running sum per ID

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-02-2015 11:07 AM

Hi all,

I'm new at using SAS and am not proficient in BASE SAS. I normally use Proc SQL for my needs.

However, it seems the LAG function is not available for Proc SQL so I'm having to use Base, which is giving me problems.

I'm trying to calculate a running sum of a value over an ID.

This is my example dataset:

data INPUT;

input ID $2. LINENR VALUE;

datalines;

A 1 1

A 2 5

A 3 2

A 4 8

B 1 4

B 2 3

B 3 4

C 1 2

D 1 7

E 1 6

E 2 3

E 3 1

E 4 7

E 5 4

F 1 2

G 1 8

G 2 9

;

run;

The output I'm looking for would be in Variable1.

ID | LINENR | VALUE | VAR1 |

A | 1 | 1 | 1 |

A | 2 | 5 | 6 |

A | 3 | 2 | 8 |

A | 4 | 8 | 16 |

B | 1 | 4 | 4 |

B | 2 | 3 | 7 |

B | 3 | 4 | 11 |

C | 1 | 2 | 2 |

D | 1 | 7 | 7 |

E | 1 | 6 | 6 |

E | 2 | 3 | 9 |

E | 3 | 1 | 10 |

E | 4 | 7 | 17 |

E | 5 | 4 | 21 |

F | 1 | 2 | 2 |

G | 1 | 8 | 8 |

G | 2 | 9 | 17 |

I have already sorted my dataset by ID and Linenumber.

My code is :

data OUTPUT;

set INPUT;

if ID = lag1(ID) then VAR1 = VALUE + lag1(VALUE) ;

else VAR1 = VALUE ;

if ID = lag1(ID) then VAR2 = VALUE + lag1(VAR2) ;

else VAR2 = VALUE ;

run;

A simpel lag value adds the previous value to the current value for an ID, in VAR1. But somehow the 2nd row of the output shows an empty value. Why is this ?

As I am looking for a running sum within an ID I also need to add the other values. VAR2 is an attempt to do this. But it seems I cannot reference the variable itself.

How do I fix my code so I have a running sum within an ID value ?

Thank you

Accepted Solutions

Solution

01-02-2015
11:14 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Keith0001

01-02-2015 11:14 AM

All Replies

Solution

01-02-2015
11:14 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Keith0001

01-02-2015 11:14 AM

data want;

set input;

by id;

if first.id then var1=0;

var1+value;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to stat_sas

01-02-2015 11:35 AM

Thank you stat@sas , your answer works.

Mohamed, thank you. However your answer gives the wrong result. Its only adding the current record and the previous one per ID.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Keith0001

01-02-2015 11:34 AM

Regarding your desired output

data OUTPUT (drop=x);

set INPUT;

by ID;

x=lag1(Value);

If first.ID then VAR1=VALUE;

Else VAR1 = x + Value ;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Keith0001

01-03-2015 02:45 AM

You also could get it by SQL ,since you have already COUNT variable LINENR .

data INPUT; input ID $2. LINENR VALUE; datalines; A 1 1 A 2 5 A 3 2 A 4 8 B 1 4 B 2 3 B 3 4 C 1 2 D 1 7 E 1 6 E 2 3 E 3 1 E 4 7 E 5 4 F 1 2 G 1 8 G 2 9 ; run; proc sql; create table want as select *,(select sum(value) from input where id=a.id and linenr le a.linenr) as var1 from input as a; quit;

Xia Keshan

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

01-05-2015 11:27 AM

Hi Xia,

Thank you, this works very well for my requirements.