4

The data set looks like

Val1    Sum of last 5 (Non Blank)
10      . 
.       10 
20      10 
30      30 
.       60 
40      60 
50      100 
30      150 
.       170 
.       170 
10      170 
20      160 
30      150 
40      130 
.       130

I need to write a code which calculates the second column, which is the sum of last five values present in column1.

flag

3 Answers

-1

This will take the sum of the last 5 values (even if they are missing). This doesn't quite mirror the expected outputs though.

/* Input Data */
data have;
input x target @@;
cards;
10 . 
.  10 
20 10 
30 30 
.  60 
40 60 
50 100 
30 150 
.  170 
.  170 
10 170 
20 160 
30 150 
40 130 
.  130 
;

run;

/* Calculate the new Var */
data need;
set have;

new_target = coalesce(lag1(x),0) + coalesce(lag2(x),0) + 
             coalesce(lag3(x),0) + coalesce(lag4(x),0) + coalesce(lag5(x),0) ;

run;
link|flag
1

To get the same results as the 1st answer and if you have SAS/ETS you can use PROC EXPAND:

proc expand data=z out=sum;     
convert x=sum_X /method=none transformout=(movsum 5);
run;

If you want to skip blanks a bit more logic needs to be added to the 1st answer.

data z;
input x @@;
cards;
10 . . 10 20 10 30 30 . 60 40 60 50 100 30 150 . 170 . 170 10 170 20 160 30 150 40 130 . 130 
;
run;

DATA z2;
SET z;
RETAIN val1 val2 val3 val4 val5 val6 val7 val8 val9 val10 ;
ARRAY val val1-val10; 
DO i=10 TO 2 BY -1;
 val{i}=val{i-1};
END; 
val1=x; 
if  n(OF val1-val5)=5 then  sum_x2=SUM(OF val1-val5); else
if  n(OF val1-val6)=5 then  sum_x2=SUM(OF val1-val6); else
if  n(OF val1-val7)=5 then  sum_x2=SUM(OF val1-val7); else
if  n(OF val1-val8)=5 then  sum_x2=SUM(OF val1-val8); else
if  n(OF val1-val9)=5 then  sum_x2=SUM(OF val1-val9); else
if n(OF val1-val10)=5 then sum_x2=SUM(OF val1-val10); 
DROP val1 val2 val3 val4 val5 val6 val7 val8 val9 val10 ;
run;
link|flag
5

Hello,

I've updated my code to take into account possible missings. Let x is your variable and sum_x the result:

DATA z2;  
SET z;  
RETAIN val1 val2 val3 val4 val5 0;  
ARRAY val val1-val5;  

IF x NE .  
THEN  
DO;  
   DO i=5 TO 2 BY -1;    
     val{i}=val{i-1};  
   END;  
   val1=x;  
END;

sum_x=SUM(OF val1-val5);  
RUN;

Kind regards,
Toloc

link|flag

Your Answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.