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
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.
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.
Set the missings to zero, and then when you do a sum and add zero into the sum ... well, you know ...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.