Proc iml: imputation using moving average from the three previous or next three records

[ev_sfn]

Hello,

So, the cells are grouped and sorted by month

I want for each cell to impute the missing values of the sales with the 3 previous months sales. If the month is in the first three months in which i dont have previous month sales i need to impute with the next 3 sales values. Also if the 2 missing sales are for example in months: 201807 201808 i need to fill up the 201807 based on the average of the previous 3 months.

You can find a sample of the dataset in the attached file.

I am trying a way to do it with proc iml because it is a big dataset.

Is there a way to impute this values easy and efficient?

The problem is that the sales can be missing in different months. If the missing month is in the first date entry for each id we need to impute with the MA of the next three sales. 

Any help on this or any recommendations @Rick_SAS  ?

Thank you in advance!

[Rick_SAS]

Yes. Since this is a repeat of https://communities.sas.com/t5/Statistical-Procedures/Proc-iml-imputation-using-moving-average-from-... and since you marked a correct answer in that thread, I recommend that you use that solution.

[ev_sfn]

Thank you for your quick response.

The answer works but it is not what I need to do  for the specific problem.

[PeterClemmensen]

Take a look at my recent post. We'll make it work 🙂

[ev_sfn · Accepted solution]

Hello all,

I found the solution to the problem.

I would like to thank you all for your responses!

You can find the code in the txt attached.

Maybe this is not the most efficient way but it worked. 

Any recommendations to make it more efficient (less code) - are welcome.