Help using Base SAS procedures

how to sum within column with 2 sorting variables?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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


Accepted Solutions
Solution
‎08-07-2011 11:23 AM
PROC Star
Posts: 7,364

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;

View solution in original post


All Replies
Solution
‎08-07-2011 11:23 AM
PROC Star
Posts: 7,364

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;

Occasional Contributor
Posts: 10

how to sum within column with 2 sorting variables?

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

☑ This topic is solved.

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

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