topic How to perform the rolling sum without missing (SAS UE)? in SAS Programming

How to perform the rolling sum without missing (SAS UE)?

likich — Thu, 07 Nov 2019 17:14:06 GMT

So I am trying to do the rolling sum (5 points) in SAS UE adding lags. I also want to get rid of missings, but they are still generated. What is wrong?

I tried doing if statements separately but didn't work...

data SUM;
set work.IMPORT;
rollingsum = y+lag1(y)+lag2(y)+lag3(y)+lag4(y);
if x=-10 then rollingsum = y;
if x=(-9.9) then rollingsum = y+lag1(y);
if x=(-9.8) then rollingsum = y+lag1(y)+lag2(y);
if x=(-9.7) then rollingsum = y+lag1(y)+lag2(y)+lag3(y);
run;

obs x y rollingsum
1 -10 0.5440211109 0.5440211109
2 -9.9 0.4575358938 .
3 -9.8 0.3664791293 .
4 -9.7 0.2717606264 .
5 -9.6 0.1743267812 1.8141235416
6 -9.5 0.0751511205 1.3452535511
7 -9.4 -0.024775425 0.8629422319
8 -9.3 -0.124454424 0.3720086791
9 -9.2 -0.222889914 -0.122641861

I want to have

obs x y rollingsum
1 -10 0.5440211109 0.5440211109
2 -9.9 0.4575358938 0.5440211109 + 0.4575358938
3 -9.8 0.3664791293 0.5440211109 + 0.4575358938 + 0.3664791293
4 -9.7 0.2717606264 ...+ 0.2717606264
5 -9.6 0.1743267812 1.8141235416
6 -9.5 0.0751511205 1.3452535511
7 -9.4 -0.024775425 0.8629422319
8 -9.3 -0.124454424 0.3720086791
9 -9.2 -0.222889914 -0.122641861

Re: How to perform the rolling sum without missing (SAS UE)?

ballardw — Thu, 07 Nov 2019 20:41:35 GMT

Use the SUM function instead of +. When you use the plus sign and any of the values are missing the result will be missing.

However if you use

if x=(-9.7) then rollingsum = sum (y,lag1(y),lag2(y),lag3(y));

that doesn't happen.

However you will run into a classic issue about use of LAG with IF. The lag function retains separate queues.

You would actually do better with

data SUM;
   set work.IMPORT;
   ly1 = lag1(y);
   ly2 = lag2(y);
   ly3 = lag3(y);
   ly4 = lag4(y);

   rollingsum = sum(y, ly1, ly2, ly3, lyr);
   if x=-10 then rollingsum = y;
   if x=(-9.9) then rollingsum = sum(y, ly1);
   if x=(-9.8) then rollingsum = sum(y, ly1, ly2);
   if x=(-9.7) then rollingsum = sum(y, ly1, ly2, ly3);
   drop ly1 - ly4;
run;

You could run that code without the drop statement to see if the proper values of y are being used.

Re: How to perform the rolling sum without missing (SAS UE)?

PaigeMiller — Thu, 07 Nov 2019 21:39:32 GMT

Set the missings to zero, and then when you do a sum and add zero into the sum ... well, you know ...

Re: How to perform the rolling sum without missing (SAS UE)?

likich — Thu, 07 Nov 2019 21:45:04 GMT

It is brilliant! Thank you so much!