I want for each cell to impute the missing values of the sales.
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.
If you have SAS/ETS, I agree with @PaigeMiller, that is probably the right tool. Yes, you can do this with IML and I'm sure users on the community can develop a flawless solution. However, I don't think it is the right tool.
I am unsure what you mean by "but in that case i have to add 6 columns" What 6 columns. In your posted data, sales is the only column of interest?
Consider the approach below. If a sales value is missing, I simply sum the sales values for the next three dates within the cell and divide it by three. I think this is what you want. Let me know if this works for you and feel free to ask 🙂
data want(keep = cell date sales);
dcl hash h();
h.definekey("cell", "date");
h.definedata("sales");
h.definedone();
do until (last.cell);
set have;
by cell;
h.add();
end;
do until (last.cell);
set have;
by cell;
s = 0;
if sales = . then do;
do i = 1 to 3;
rc = h.find(key : cell, key : intnx('month', date, i, 'b'));
s + sales;
end;
sales = divide(s, 3);
end;
output;
end;
h.clear();
run;
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.
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.
If you have SAS/ETS, I agree with @PaigeMiller, that is probably the right tool. Yes, you can do this with IML and I'm sure users on the community can develop a flawless solution. However, I don't think it is the right tool.
I am unsure what you mean by "but in that case i have to add 6 columns" What 6 columns. In your posted data, sales is the only column of interest?
Consider the approach below. If a sales value is missing, I simply sum the sales values for the next three dates within the cell and divide it by three. I think this is what you want. Let me know if this works for you and feel free to ask 🙂
data want(keep = cell date sales);
dcl hash h();
h.definekey("cell", "date");
h.definedata("sales");
h.definedone();
do until (last.cell);
set have;
by cell;
h.add();
end;
do until (last.cell);
set have;
by cell;
s = 0;
if sales = . then do;
do i = 1 to 3;
rc = h.find(key : cell, key : intnx('month', date, i, 'b'));
s + sales;
end;
sales = divide(s, 3);
end;
output;
end;
h.clear();
run;
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).
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.
I may misunderstand you. But it seems to me like you describe two different logics.
1) If a value is missing look at the three latest dates. If one of them is missing, look one date ahead and take that value into account. If two of the 'lags' are missing, consider two leads and so on..
2) If we are on one of the first three dates for a cell, consider the next three dates. If not, consider the three previous dates.
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.
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).
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!
What is ANOVA?
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
