SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to complete missing values with previous value

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

How to complete missing values with previous value

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

 

 


Accepted Solutions
Solution
‎04-21-2016 05:53 PM
Respected Advisor
Posts: 4,920

Re: How to complete missing values with previous value

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;
PG

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: How to complete missing values with previous value

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;
Contributor
Posts: 58

Re: How to complete missing values with previous value

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

Re: How to complete missing values with previous value

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.
Trusted Advisor
Posts: 1,117

Re: How to complete missing values with previous value

[ Edited ]

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.

Respected Advisor
Posts: 3,799

Re: How to complete missing values with previous value

[ Edited ]
Posted in reply to FreelanceReinhard

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;

 

Respected Advisor
Posts: 4,920

Re: How to complete missing values with previous value

Posted in reply to data_null__

Woah! Smiley Happy

PG
Solution
‎04-21-2016 05:53 PM
Respected Advisor
Posts: 4,920

Re: How to complete missing values with previous value

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;
PG
Contributor
Posts: 58

Re: How to complete missing values with previous value

Thans PG, this work absolutely perfect.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 500 views
  • 8 likes
  • 5 in conversation