Calcite | Level 5

## how to sum within column with 2 sorting variables?

Hello,

Suppose i have such dataset :

date   id    res     sumres

1       a       10

1       a       15

1       a       10       35

2       a       12

2       a        8

2       a       10       30

1       b       10

1       b       17

1       b       12       39

;

I need to calculate these sumres (35, 30, 39....) in a new dataset which are for id=a and date=1, for id=a and date=2, id=b and date=1, id=b=2 and so on... i have dates from 1...30.

its probably a loop,  if first.date=....do;..but cant figure it out.

anyone knows how to do it? i'd be immesely grateful.

cheers,

M

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## how to sum within column with 2 sorting variables?

If your data are EXACTLY as shown, and you really only want the sumres calculation to be non-missing for the last record in each set, then it is easy.  I don't have access to SAS to test the following code at the moment, but it should give you enough of an idea to solve your problem.

data want (drop=calculation);

set have;

by id date;

if first.date then calculation=res;

else calculation+res;

if last.date then sumres=calculation;

run;

2 REPLIES 2
Opal | Level 21

## how to sum within column with 2 sorting variables?

If your data are EXACTLY as shown, and you really only want the sumres calculation to be non-missing for the last record in each set, then it is easy.  I don't have access to SAS to test the following code at the moment, but it should give you enough of an idea to solve your problem.

data want (drop=calculation);

set have;

by id date;

if first.date then calculation=res;

else calculation+res;

if last.date then sumres=calculation;

run;

Calcite | Level 5

## how to sum within column with 2 sorting variables?

art297, thank you for a quick reply   - you're a star!   i tried it before but missed out the ' if last.date' part

Discussion stats
• 2 replies
• 631 views
• 0 likes
• 2 in conversation