topic Re: Replacing Missing values with average in SAS Programming

Replacing Missing values with average

georgel — Sun, 03 Jan 2021 17:34:27 GMT

Hi to all , Wish you Happy New Year 2021!

I would like to replace missing values with average and the rest that have missing till the end to leave them as are.

data have;
input cusip_id $8. Name $6. date_issue :ddmmyy10. type $5. Ret  dollar8.1;
format date_issue ddmmyy10. ;
datalines;
10343452 Zilas 01/04/2016 main  3.2
10343452 Zilas 02/04/2014 main     4.5
10343452 Zilas 03/04/2012 main   6.6
10343452 Zilas 04/04/2011 main   .
10343452 Zilas 05/04/2017 main  7.7
10343452 Zilas 06/04/2017 main  .
10343452 Zilas 07/04/2017 main  .
10343452 Zilas 08/04/2017 main  .
10343453 Astra 01/04/2016 main  6.2
10343453 Astra 02/04/2014 main     5.5
10343453 Astra 03/04/2012 main   2.6
10343453 Astra 04/04/2011 main   .
10343453 Astra 05/04/2011 main   .
10343453 Astra 06/04/2017 main  4.7
10343453 Astra 07/04/2017 main  .
10343453 Astra 08/04/2017 main  8
10343453 Astra 09/04/2017 main  .
10343453 Astra 10/04/2017 main  .
10343453 Astra 11/04/2017 main  .
10343453 Astra 12/04/2017 main  .
10343453 Astra 13/04/2017 main  .
;
run;

data want;
input cusip_id $8. Name $6. date_issue :ddmmyy10. type $5. Ret  dollar8.1;
format date_issue ddmmyy10. ;
datalines;
10343452 Zilas 01/04/2016 main  3.2
10343452 Zilas 02/04/2014 main     4.5
10343452 Zilas 03/04/2012 main   6.6
10343452 Zilas 04/04/2011 main   7.15
10343452 Zilas 05/04/2017 main  7.7
10343452 Zilas 06/04/2017 main  .
10343452 Zilas 07/04/2017 main  .
10343452 Zilas 08/04/2017 main  .
10343453 Astra 01/04/2016 main  6.2
10343453 Astra 02/04/2014 main     5.5
10343453 Astra 03/04/2012 main   2.6
10343453 Astra 04/04/2011 main   3.65
10343453 Astra 05/04/2011 main   3.65
10343453 Astra 06/04/2017 main  4.7
10343453 Astra 07/04/2017 main  6.35
10343453 Astra 08/04/2017 main  8
10343453 Astra 09/04/2017 main  .
10343453 Astra 10/04/2017 main  .
10343453 Astra 11/04/2017 main  .
10343453 Astra 12/04/2017 main  .
10343453 Astra 13/04/2017 main  .

;
run;

Many thanks in advance

Re: Replacing Missing values with average

novinosrin — Sun, 03 Jan 2021 17:06:13 GMT

Hi @georgel Can you please explain average of what?

1. Is it average of prior and after non missing value?

2. If yes, how did you get

10343453 Astra 04/04/2011 main   5.15
10343453 Astra 05/04/2011 main   5.15

Re: Replacing Missing values with average

georgel — Sun, 03 Jan 2021 17:23:39 GMT

Hi novinosrin,

1. Yes . 2. (4.7+2.6)/2=3.65 I made a mistake in my initial table.

Many thanks in advance

Regards

George

Re: Replacing Missing values with average

mkeintz — Sun, 03 Jan 2021 17:32:22 GMT

Let's clarify what you are asking for.

You apparently want to replace only missing values that fall into a gap between non-missing values of ret.

At first, I thought you wanted to merely assign the mid-point between the closest preceding and closest following non-missing values. That would work for rows 4 (gets ret=7.15 from 6.6 and 7.7) and 15 (6.35 from 4.7 and 8). But it doesn't explain rows 12 and 13 (they get 5.15 - but are preceded by 2.6 and followed by 4.7).

Please explain.

Re: Replacing Missing values with average

georgel — Sun, 03 Jan 2021 17:36:39 GMT

Hi Keintz

I have corrected my initial post to be the average 3.65

Thanks

Re: Replacing Missing values with average

LeonidBatkhan — Sun, 03 Jan 2021 17:49:03 GMT

Hi georgel,

Here is one possible solution (I am not sure if it is the most efficient one):

data have;
input cusip_id $8. Name $6. date_issue :ddmmyy10. type $5. Ret  dollar8.1;
format date_issue ddmmyy10. ;
datalines;
10343452 Zilas 01/04/2016 main  3.2
10343452 Zilas 02/04/2014 main     4.5
10343452 Zilas 03/04/2012 main   6.6
10343452 Zilas 04/04/2011 main   .
10343452 Zilas 05/04/2017 main  7.7
10343452 Zilas 06/04/2017 main  .
10343452 Zilas 07/04/2017 main  .
10343452 Zilas 08/04/2017 main  .
10343453 Astra 01/04/2016 main  6.2
10343453 Astra 02/04/2014 main     5.5
10343453 Astra 03/04/2012 main   2.6
10343453 Astra 04/04/2011 main   .
10343453 Astra 05/04/2011 main   .
10343453 Astra 06/04/2017 main  4.7
10343453 Astra 07/04/2017 main  .
10343453 Astra 08/04/2017 main  8.0
10343453 Astra 09/04/2017 main  .
10343453 Astra 10/04/2017 main  .
10343453 Astra 11/04/2017 main  .
10343453 Astra 12/04/2017 main  .
10343453 Astra 13/04/2017 main  .
;
run;

proc sort data=HAVE;
   by cusip_id;
run;

data PASS1;
   set HAVE;
   by cusip_id;
   retain R1;
   Order = _n_;
   if first.cusip_id then R1 = .;
   if Ret ne . then R1 = Ret;
run;

proc sort data=PASS1 out=PASS2;
   by descending Order;
run;

data PASS3;
   set PASS2;
   by descending cusip_id;
   retain R2;
   if first.cusip_id then R2 = .;
   if Ret ne . then R2 = Ret;
run;

proc sort data=PASS3 out=PASS4;
   by Order;
run;

data WANT (drop=R1 R2 Order);
   set PASS4;
   if (Ret=.) and (R1 ne .) and (R2 ne .) then Ret = (R1+R2)/2;
run;

Please note, that I changed one line in your raw data to be

10343453 Astra 08/04/2017 main 8.0

If you have 8 instead of 8.0 your data will be read in as 0.8 .

Hope this helps.

Re: Replacing Missing values with average

novinosrin — Sun, 03 Jan 2021 17:57:03 GMT


data have;
input cusip_id $8. Name $6. date_issue :ddmmyy10. type $5. Ret  dollar8.1;
format date_issue ddmmyy10. ;
datalines;
10343452 Zilas 01/04/2016 main  3.2
10343452 Zilas 02/04/2014 main     4.5
10343452 Zilas 03/04/2012 main   6.6
10343452 Zilas 04/04/2011 main   .
10343452 Zilas 05/04/2017 main  7.7
10343452 Zilas 06/04/2017 main  .
10343452 Zilas 07/04/2017 main  .
10343452 Zilas 08/04/2017 main  .
10343453 Astra 01/04/2016 main  6.2
10343453 Astra 02/04/2014 main     5.5
10343453 Astra 03/04/2012 main   2.6
10343453 Astra 04/04/2011 main   .
10343453 Astra 05/04/2011 main   .
10343453 Astra 06/04/2017 main  4.7
10343453 Astra 07/04/2017 main  .
10343453 Astra 08/04/2017 main  8
10343453 Astra 09/04/2017 main  .
10343453 Astra 10/04/2017 main  .
10343453 Astra 11/04/2017 main  .
10343453 Astra 12/04/2017 main  .
10343453 Astra 13/04/2017 main  .
;
run;


data want;
 if _n_ = 1 then do ;
   dcl hash h() ;
   h.definekey ("_n") ;
   h.definedata ("ret" ) ;
   h.definedone () ;
 end ; 
 do __n=1 by 1 until(last.name);
  set have;
  by cusip_id name notsorted;
  if ret=. and not _k then  _k=__n;
  if ret then do;
   if _k>. then if __n-_k>=1 then do;
     _ret=mean(ret,_ret);
     do _n=_k to __n-1;
	  h.add(key:_n,data:_ret);
	 end;
	 _k=.;
   end;
   _ret=ret;
  end;
 end;
 do __n=1 to __n;
  set have;
  _n_=h.find(key:__n);
  output;
 end;
 h.clear();
 drop _:;
run;

Re: Replacing Missing values with average

mkeintz — Sun, 03 Jan 2021 18:07:37 GMT

You need code to capture the most recent non-missing and the upcoming non-missing. You can do this by passing through all the records for a given cusip_id and storing them in an array. Whenever a non-missing is encountered, fill in any preceding element of the array that are missing using the average of the current and most recent non-missing.

Then re-read the same cusip_id. whenever a missing ret if found, retrieve values from the array:

data have;
input cusip_id $8. Name $6. date_issue :ddmmyy10. type $5. Ret  dollar8.1;
format date_issue ddmmyy10. ;
datalines;
10343452 Zilas 01/04/2016 main  3.2
10343452 Zilas 02/04/2014 main     4.5
10343452 Zilas 03/04/2012 main   6.6
10343452 Zilas 04/04/2011 main   .
10343452 Zilas 05/04/2017 main  7.7
10343452 Zilas 06/04/2017 main  .
10343452 Zilas 07/04/2017 main  .
10343452 Zilas 08/04/2017 main  .
10343453 Astra 01/04/2016 main  6.2
10343453 Astra 02/04/2014 main     5.5
10343453 Astra 03/04/2012 main   2.6
10343453 Astra 04/04/2011 main   .
10343453 Astra 05/04/2011 main   .
10343453 Astra 06/04/2017 main  4.7
10343453 Astra 07/04/2017 main  .
10343453 Astra 08/04/2017 main  8.0    /*changed 8 to 8.0 to accomodate dollar8.1 format*/
10343453 Astra 09/04/2017 main  .
10343453 Astra 10/04/2017 main  .
10343453 Astra 11/04/2017 main  .
10343453 Astra 12/04/2017 main  .
10343453 Astra 13/04/2017 main  .
run;


data want (drop=_:);
  array rets {30} _temporary_;
  call missing(of rets{*});
  do _i=1 by 1 until (last.cusip_id);
    set have;
    by cusip_id;
    if ret^=. then do;
      _mean_ret=mean(lag(ret),ret);
      if _i> 1 then do _j=_i-1 to 1 by -1 while(rets{_j}=.);
	    rets{_j}=_mean_ret;
	  end;
    end;
  end;

  do _i=1 by 1 until (last.cusip_id);
    set have;
	by cusip_id;
    if missing(ret) then ret=rets{_i};
    output;
  end;
run;

A note:

The mean(lag(ret),ret) only retrieves the lagged value when the current ret is not missing. This means that the lag(ret) will also always be non-missing (except at the beginning). That's because the LAG function is actually a fifo queue, which is only updated when ret is not missing.

Also, make the array size at least as large as the most populated CUSIP_ID. I used 30 above.

Re: Replacing Missing values with average

georgel — Sun, 03 Jan 2021 18:11:32 GMT

Impressive! Thanks a million Keintz

Re: Replacing Missing values with average

georgel — Sun, 03 Jan 2021 18:13:42 GMT

Many thanks novinosrin. I have tried it but one observation has been removed

Re: Replacing Missing values with average

georgel — Sun, 03 Jan 2021 18:15:00 GMT

Thanks Leonid

Re: Replacing Missing values with average

novinosrin — Sun, 03 Jan 2021 18:18:02 GMT

Oh sorry, which one did it drop or remove?

Re: Replacing Missing values with average

georgel — Sun, 03 Jan 2021 18:26:22 GMT

novinosrin, Accept my apologies I rerun and it works perfectly!.

I admit that your solution is more efficient since the accepted solution I have to resize the matrix

Perfect work! Thanks a million novinosrin

Regards

George

Re: Replacing Missing values with average

novinosrin — Sun, 03 Jan 2021 18:28:59 GMT

No my solution is not efficient than @mkeintz 's solution. Temp ARRAY is much faster. As a matter of fact, I don't think any of my solutions can come close to Mark's and in fact he was the one who taught me many stuff in SAS over the last 5 years. Oh gosh, Mark must have gotten much older lol hahaha

Re: Replacing Missing values with average

mkeintz — Sun, 03 Jan 2021 19:10:13 GMT

Question: why are your data not ordered chronologically? For instance the 3rd-5th rows are as below (note dates are dd/mm/yyyy):

10343452 Zilas 03/04/2012 main   6.6
10343452 Zilas 04/04/2011 main   .
10343452 Zilas 05/04/2017 main  7.7

So you are estimating data for 4/4/2011 (04apr2011) from 03apr2012 and 05april2017. Really? 2011 from 2012 and 2017?

Re: Replacing Missing values with average

georgel — Sun, 03 Jan 2021 19:54:41 GMT

Dear Mark,

Thank you very much for paying attention to details!. This is a great support from you and I highly appreciated it.

No, this is just a random sample non-real one!! I have my real data. I havent notice this!. Basically to telll the story. I want to have benchmarks for each of my own set of companies all of them from US but I find that some of my benchmarks have only one missing return. The algortihm that I created it finds another benchmark-company only for the missing day and I dont want this. (real example from CRSP: WRDS:20030815 CUSIP: 88800230 Missing Return )

Regards

George

Re: Replacing Missing values with average

mkeintz — Sun, 03 Jan 2021 23:16:33 GMT

If your data are already sorted, then there is a somewhat cleaner way to replace internal missing values:

data retmeans (keep=cusip_id date_issue _midpoint) / view=retmeans;
  merge have (where=(ret^=.)                 keep=cusip_id ret date_issue)
        have (where=(_nxt_ret^=.) firstobs=2 keep=cusip_id ret rename=(cusip_id=_nxt_cusip ret=_nxt_ret));
  if cusip_id=_nxt_cusip then _midpoint=mean(ret,_nxt_ret);
run;

data want (drop=_:);
  merge have  retmeans (keep=cusip_id date_issue in=inmid);
  by cusip_id date_issue;
  if inmid then set retmeans ; /* Get _MIDPOINT */
  if ret=. then ret=_midpoint;
run;

Note the data set RETMEANS is a data set view, not a data set file. That means it will not be written to disk, and that it will only be implemented when called for in a later data step. Think of it as a synchronized process that is run only when used in the DATA WANT step. So while the program is structed in two steps, there is only one step that actually causes data processing - i.e. it's efficient.

The retmeans data set will have one record per non-missing RET. It will contain the id, date and the _MIDPOINT, the mean of the current RET and the following RET (note the FIRSTOBS=2). Except for the last non-missing RET of each ID where _MIDPOINT will be left missing.

The second data step refers to RETMEANS once in a MERGE statement (excluding the _MIDPOINT variable) and once the SET statement. Any variable in the (unconditional) MERGE statement will be replaced with ever iteration of the data step. But any variable associated only with the (conditional) SET statement will not be replaced/reset until the next execution of the set statement. So _MIDPOINT will be retained and available for all dates between two non-missing RET's.

Re: Replacing Missing values with average

Ksharp — Mon, 04 Jan 2021 12:04:25 GMT

data have;
input cusip_id $8. Name $6. date_issue :ddmmyy10. type $5. Ret ;
format date_issue ddmmyy10. ;
datalines;
10343452 Zilas 01/04/2016 main  3.2
10343452 Zilas 02/04/2014 main     4.5
10343452 Zilas 03/04/2012 main   6.6
10343452 Zilas 04/04/2011 main   .
10343452 Zilas 05/04/2017 main  7.7
10343452 Zilas 06/04/2017 main  .
10343452 Zilas 07/04/2017 main  .
10343452 Zilas 08/04/2017 main  .
10343453 Astra 01/04/2016 main  6.2
10343453 Astra 02/04/2014 main     5.5
10343453 Astra 03/04/2012 main   2.6
10343453 Astra 04/04/2011 main   .
10343453 Astra 05/04/2011 main   .
10343453 Astra 06/04/2017 main  4.7
10343453 Astra 07/04/2017 main  .
10343453 Astra 08/04/2017 main  8
10343453 Astra 09/04/2017 main  .
10343453 Astra 10/04/2017 main  .
10343453 Astra 11/04/2017 main  .
10343453 Astra 12/04/2017 main  .
10343453 Astra 13/04/2017 main  .
;
run;
data want;
if 0 then set have;
retain lag_ret .;
do until(last.cusip_id or not missing(ret));
 set have;
 by cusip_id;
 if first.cusip_id then call missing(lag_ret);
end;
mean=(lag_ret+ret)/2;
do until(last.cusip_id or not missing(ret));
 set have;
 by cusip_id;
 new_ret=coalesce(ret,mean);
 output;
end;
lag_ret=ret;
drop mean lag_ret ret;
run;

Re: Replacing Missing values with average

georgel — Mon, 04 Jan 2021 17:39:28 GMT

Dear Mark,

Thank you very much for your great support and explanations.

Regards

George

Re: Replacing Missing values with average

Rick_SAS — Mon, 04 Jan 2021 18:37:30 GMT

Just use PROC STDIZE, as described in this article. But also be aware that there are disadvantages to replacing missing values by the mean of the nonmissing values.