BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ev_sfn
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ev_sfn
Obsidian | Level 7

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 solution in original post

4 REPLIES 4
Rick_SAS
SAS Super FREQ

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
Obsidian | Level 7

Thank you for your quick response.

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

ev_sfn
Obsidian | Level 7

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

From The DO Loop
Want more? Visit our blog for more articles like these.
Discussion stats
  • 4 replies
  • 1330 views
  • 2 likes
  • 3 in conversation