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! ... View more

Alright! Here we have only one ID. The problem is that for this ID we  have 2 missing records in the sales. The 2 missing sales could be anywhere! Please see here some of possible cases of missing sales for months: 1)we may have 2 missing sales in months 201811,201812 -> in this case we want to get the average of the next 3 months so the program should impute the sales from month :201812 first (from months:201901,201902,201903) ,and then to impute 201811 sales from the average of the three next including sales of (201812,201901,201902) 2)we may have 2 missing sales in months 201811,201902 -> in this case it should impute the sales of 201902 month first (from the avg of the sales from months:201903,201904,201905) and when it is filled with a value  it should impute the sales of month 201811 (from the avg of the sales of 201811 we imputed,from sales of 201812 and 201901) 3) we may have 2 missing sales in months 201907,201908-> in this case we need to impute first the sales of 201907 (from the avg of the previous 3 months: 201904,201905,201906) and then to impute the missing sales of 201908, because we will use the imputed value from month 201907.So,the sales of month 201908 should imputed from the avg of sales of months(201905,201906,201907-this should be the imputed value ) 4) we may have 2 missing sales in months 201911,201912(the last 2 months of an ID) -> in this case we need to impute the sales of of 201911 from the avg of the previous 3 months( 201908,201909,201910) and when it is imputed we will continue with the imputation of sales of 201912 from the avg of the previous 3 (incl. the new imputed value 201911) .So, it will be the avg of sales from months(201911,201910,201909).   I hope it makes sense now.   ... View more

I am sorry for the misunderstanding and thank you for your further answer on this. I am trying to do the 2) . However, I should consider if I have 2 missing sales in the middle (for example in months 201808,201809) in that case i need to impute the sales from 201808 by calculating the average of the 3 previous sales and then calculate the missing sales of the month 201809 by getting the average of the 3 previous(the sales from months 201808,201807,201806).   Also, in case we have the 2 missing sales in the last 3 months (ex. 202008,202010) we need to fill up first the missing sale of 202008 and then impute the missing sale of 202010 from the previous 3 sales(and 1 of them is the sales we imputed for the month 202008).  Ypu can check the sample to understand what i mean if we have missing sales in the last months, or in the middle or in the beggining. I hope that's more clear now.  ... View more

Thank you for your quick response. The answer works but it is not what I need to do  for the specific problem. ... View more

this is not exactly what I am trying to do here. I am trying to impute the sales from 3 previous sales and if I am in the first 3 months(I cant take the 3 previous months because there are no sales) in that case I want to impute with the next 3 months. ... View more

Thank you! I mean that I thought to get 6 columns by using proc expand to get (lag1,lag2,lag3,lead1,lead2,lead3) and then everytime a missing value of sales found it could fill from the mean of the three values of the lags or leads. So if the row had missing in the 3 lags it would fill up with leads otherwise it would move on to the next missing. And then I would drop all this columns. Then I would made them again to impute the 2nd missing value of each id. I thought this would consume too many resources. With proc expand how could i make it differently?   This code works for the missing in the first dates but it doesnt work if the sales missing in the last 2 dates (the last 2 months of each ID).   ... View more

Thank you for your response. I tried with proc expand but the way i did it was to create 6 columns with the leads and the lags so I can get the average of the three lags or leads based on the missing in the sales. Maybe is wrong my thinking on this because i dont know if i can fill up the missings with the lag / lead of the 3 previous/next values straight away. ... View more

Thank you for your help! ... View more

I tried to solve this by using leads and lags but in that case i have to add 6 columns (lag1,lag2,lag3,lead1,lead2,lead3) and then get the avg of the lags or leads if there is no measing in the fields of lags in each row else it will calculate the leads  I thought it will be more efficient using proc iml and flags in positions 1 or 2 based on the place the sale is missing. Do you think it would not work with proc iml? ... View more