DATA Step, Macro, Functions and more

Please help me out the Cumulative sum using first. condition

Reply
Occasional Contributor
Posts: 5

Please help me out the Cumulative sum using first. condition

DATA TEST;
INPUT ID$ CTR IS AS2;
CARDS;
AA 0 0 30
AA 2 3 30
AA 28 28 30
BB 10 5 22
BB 0 0 22
BB 12 4 22
;
RUN;

 

OUTPUT:

ID CTR IS A2 A1
AA 0 7 30 30
AA 2 3 30 33
AA 28 28 30 61
BB 0 9 22 22
BB 0 3 22 22
BB 10 5 22 27
BB 12 4 22 31

 

IF FIRST.ID THEN DO;

A1=A2

AND SAME LOOP IF CTR IS 0 THEN A1=A2 ELSE A1+IS(CUMULATIVE SUM)

 

Super User
Posts: 10,681

Re: Please help me out the Cumulative sum using first. condition

Posted in reply to Bhargavi221
DATA TEST;
INPUT ID$ CTR IS AS2;
CARDS;
AA 0 0 30
AA 2 3 30
AA 28 28 30
BB 10 5 22
BB 0 0 22
BB 12 4 22
;
RUN;
data want;
 set test;
 by id;
 if first.id then sum=as2;
  else sum+is;
run;
Occasional Contributor
Posts: 5

Re: Please help me out the Cumulative sum using first. condition

Ksharp, Thank you so much for the update.

 

DATA TEST;
INPUT ID$ CTR IS AS2;
CARDS;
AA 0 0 30
AA 2 3 30
AA 28 28 30
BB 10 5 22
BB 0 6 22
BB 12 4 22
;
RUN;

This case your code is summing up to (BB 0 6 22 ) 28, but i need if CTR=0 Then sum= as2 (don't add IS to sum when CTR =0)

Like below:

ID CTR IS AS2 sum
AA 0 0 30 30
AA 2 3 30 33
AA 28 28 30 61
BB 10 5 22 22
BB 0 6 22 22
BB 12 4 22 26

 

 

PROC Star
Posts: 1,547

Re: Please help me out the Cumulative sum using first. condition

[ Edited ]
Posted in reply to Bhargavi221

just a very small tweak to Ksharp's code:

 

DATA TEST;
INPUT ID$ CTR IS AS2;
CARDS;
AA 0 0 30
AA 2 3 30
AA 28 28 30
BB 10 5 22
BB 0 0 22
BB 12 4 22
;
RUN;


data want;
 set test;
 by id;
 if first.id or ctr=0 then  sum=as2;
  else   sum+is;
run;

 

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 152 views
  • 0 likes
  • 3 in conversation