How to complete missing values with previous value

[fri0]

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

[RW9]

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;

[fri0]

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

[fri0]

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.

[FreelanceReinh]

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.

[data_null__]

---

@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.

1. Initalize to missing the variable that you don't way to carry forward after the output statement.
2. 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;

[PGStats]

Woah! 

[PGStats · Accepted solution]

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;

[fri0]

Thans PG, this work absolutely perfect.