topic Re: How to complete missing values with previous value in SAS Data Management

How to complete missing values with previous value

fri0 — Tue, 19 Apr 2016 15:49:46 GMT

Hi, I need your help to complete a dataset. I have many stock shares ordered by share and date. Also I have its last price of the day, but sometimes there is a missing value if there was no trading that day. I want to complete missing values with the last valid value. The amount of missing values are different by each stock shares. Thanks in advance for your help.

This is an example of my dataset:

Name Date Last

AAA 4/1/16 5.10

AAA 5/1/16 .

AAA 6/1/16 .

AAA 7/1/16 .

AAA 8/1/16 5.13

AAA 11/1/16 5.13

AAA 12/1/16 5.15

BBB 4/1/16 25.60

BBB 5/1/16 25.61

BBB 6/1/16 .

BBB 7/1/16 .

BBB 8/1/16 25.65

BBB 11/1/16 .

BBB 12/1/16 25.66

I need this:

For the second register, last price is missing, so I need to replace it with the previous value 5.10. The third register also has no value for last, so I replace with the previous value 5.10 and then until find a valid value.

Name Date Last

AAA 4/1/16 5.10

AAA 5/1/16 5.10

AAA 6/1/16 5.10

AAA 7/1/16 5.10

AAA 8/1/16 5.13

AAA 11/1/16 5.13

AAA 12/1/16 5.15

BBB 4/1/16 25.60

BBB 5/1/16 25.61

BBB 6/1/16 25.61

BBB 7/1/16 25.61

BBB 8/1/16 25.65

BBB 11/1/16 25.65

BBB 12/1/16 25.66

Re: How to complete missing values with previous value

RW9 — Tue, 19 Apr 2016 16:07:34 GMT

Well, not typing that data in, so this is untested:

data want (drop=lstlast);
  set have;
  retain lstlast;
  if last ne . then lstlast=last;
  else last=lstlast;
run;

Re: How to complete missing values with previous value

fri0 — Tue, 19 Apr 2016 16:18:26 GMT

The SAS server is down, but I'll try when it'll be operative again.

Re: How to complete missing values with previous value

FreelanceReinh — Tue, 19 Apr 2016 16:33:55 GMT

Hi @fri0,

A nice trick that I've learned from @data_null__ and @Astounding is to use the UPDATE statement as follows:

data have;
input Name $ Date :ddmmyy. Last;
format date ddmmyy8.;
cards;
AAA 4/1/16 5.10
AAA 5/1/16 .
AAA 6/1/16 .
AAA 7/1/16 .
AAA 8/1/16 5.13
AAA 11/1/16 5.13
AAA 12/1/16 5.15
BBB 4/1/16 .
BBB 5/1/16 25.61
BBB 6/1/16 .
BBB 7/1/16 .
BBB 8/1/16 25.65
BBB 11/1/16 .
BBB 12/1/16 25.66
;

data want;
update have(obs=0) have;
by name;
output;
run;

Please note that I've replaced the first value of variable LAST for name 'BBB' with a missing value to demonstrate that with the above solution values from one name are not carried forward into missing data of another name.

Edit: It should be noted that the action of the UPDATE statement is not limited to variable LAST. This means that also missing DATE values would be filled with the last available date within the BY group.

Re: How to complete missing values with previous value

PGStats — Tue, 19 Apr 2016 17:35:13 GMT

Don't forget to take care of the case where last is missing for the first record of a stock

data have;
input Name $ Date :mmddyy10. Last;
format date yymmdd10.;
datalines;
AAA 4/1/16 5.10
AAA 5/1/16 .
AAA 6/1/16 .
AAA 7/1/16 .
AAA 8/1/16 5.13
AAA 11/1/16 5.13
AAA 12/1/16 5.15
BBB 3/1/16 .
BBB 4/1/16 25.60
BBB 5/1/16 25.61
BBB 6/1/16 .
BBB 7/1/16 .
BBB 8/1/16 25.65
BBB 11/1/16 .
BBB 12/1/16 25.66
;

data want (drop=lstlast);
  set have; by name notsorted;
  retain lstlast;
  if first.name then call missing(lstLast);
  if last ne . then lstlast=last;
  else last=lstlast;
run;

proc print data=want noobs; run;

Re: How to complete missing values with previous value

data_null__ — Tue, 19 Apr 2016 20:17:39 GMT

@FreelanceReinhard wrote:

Hi @fri0,

Edit: It should be noted that the action of the UPDATE statement is not limited to variable LAST. This means that also missing DATE values would be filled with the last available date within the BY group.

That is true but there are ways to work around that.

Initalize to missing the variable that you don't way to carry forward after the output statement.
Only operate on (UPDATE) the LOCF variable(s) and get the others from SET. KEEP or DROP depending on which is the shortest list.

data patients; 
   input patientID $ year $ diagA $ diagB $ diagC $; 
   datalines; 
1 2010 . . . 
1 2011 . 1 . 
1 2012 . . 1 
1 2014 . . . 
2 2009 1 . . 
2 2010 1 . . 
2 2013 . 1 . 
2 2015 . . . 
;;;; 
   run;
%let locf=patientid diag:;
data patients;
   if 0 then set patients;
   update patients(obs=0 keep=&locf) patients(keep=&locf);
   by patientid;
   set patients(drop=&locf);
   output;
   run;
proc print;
   run;

Re: How to complete missing values with previous value

PGStats — Tue, 19 Apr 2016 20:19:25 GMT

Woah!

Re: How to complete missing values with previous value

fri0 — Thu, 21 Apr 2016 21:55:20 GMT

Thanks RW9, this work perfect when there is no missing space as first value for last. But, there were cases with first value missing, so I had to chose the PGSTats.

Re: How to complete missing values with previous value

fri0 — Thu, 21 Apr 2016 21:55:40 GMT

Thans PG, this work absolutely perfect.