DATA Step, Macro, Functions and more

Replacing the missing value with earlier date or later date

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 115
Accepted Solution

Replacing the missing value with earlier date or later date

Hi there,

I need your kind help to replace the missing value of a column with the previous date's value where as in case of missing first observation, the value will be replaced by next available value. 

data have ;
format date date9.;
input id $ LHIN 2. date date9. pulse 3. ;
datalines;
101 1 01JAN2017 90
101 1 03JAN2017 80
101 1 05JAN2017   
102 2 01JAN2017 90
102 2 03JAN2017 
102 3 05JAN2017 70
103 2 03JAN2017 
103 3 05JAN2017 70
103 3 06JAN2017 70
104 2 03JAN2017 
104 3 05JAN2017 
104 3 06JAN2017 70
;
run;



data want;
format date date9.;
input id $ LHIN 2. date date9. pulse 3. ;
datalines;
101 1 01JAN2017 90
101 1 03JAN2017 80
101 1 05JAN2017 80
102 2 01JAN2017 90
102 2 03JAN2017 90
102 3 05JAN2017 70
103 2 03JAN2017 80
103 3 05JAN2017 80
103 3 06JAN2017 70
104 2 03JAN2017 70
104 3 05JAN2017 70
104 3 06JAN2017 70
;
run;

Thank you in advance for your kind reply. 

Regards,

 

Swain

Accepted Solutions
Solution
‎11-20-2017 09:44 AM
Super User
Posts: 10,623

Re: Replacing the missing value with earlier date or later date

Posted in reply to DeepakSwain

All Replies
Super User
Super User
Posts: 9,227

Re: Replacing the missing value with earlier date or later date

Posted in reply to DeepakSwain

There is quite a few posts on this subject.  There are various methods, some guys like the update or hashtable.  Me I would just go with retained value:

data want (drop=p);
  set have (rename=(pulse=p));
  retain pulse;
  if p ne . then pulse=p;
run;
Frequent Contributor
Posts: 115

Re: Replacing the missing value with earlier date or later date

Hi RW9,

 

This code seems to be very simple and great. Is it possible to do little modification so that the value carried forward wiil be depenedednt on ID. 

 

Regards,

Swain
Super User
Super User
Posts: 9,227

Re: Replacing the missing value with earlier date or later date

Posted in reply to DeepakSwain

Yep:

data want (drop=p);
  set have (rename=(pulse=p));
by id; retain pulse;
if first.id then pulse=p; if p ne . then pulse=p; run;
Frequent Contributor
Posts: 115

Re: Replacing the missing value with earlier date or later date

Hi RW9,

Unfortunately for ID 101 ,

I am getting: 

date id LHIN pulse
01-Jan-17 101 1 90
03-Jan-17 101 1 80
05-Jan-17 101 1 70

 

Expected:

101 1 01JAN2017 90
101 1 03JAN2017 80
101 1 05JAN2017 80

 

Regards,

 

Swain
Super User
Super User
Posts: 9,227

Re: Replacing the missing value with earlier date or later date

Posted in reply to DeepakSwain

Please provide test data exactly as it is.  I have run the code:

data have ;
format date date9.;
input id $ LHIN 2. date date9. pulse 3. ;
datalines;
101 1 01JAN2017 90
101 1 03JAN2017 80
101 1 05JAN2017   
102 2 01JAN2017 90
102 2 03JAN2017 
102 3 05JAN2017 70
103 2 03JAN2017 
103 3 05JAN2017 70
103 3 06JAN2017 70
104 2 03JAN2017 
104 3 05JAN2017 
104 3 06JAN2017 70
;
run;

data want (drop=p);
  set have (rename=(pulse=p));
  by id;
  retain pulse;
  if first.id then pulse=p;
  if p ne . then pulse=p;
run;

And it shows the correct value of 80, I suspect your data is not sorted correctly or something.

Frequent Contributor
Posts: 115

Re: Replacing the missing value with earlier date or later date

Hi RW9,

It is not working for ID 103 and 104:

03-Jan-17 103 2 .
05-Jan-17 103 3 70
06-Jan-17 103 3 70
03-Jan-17 104 2 .
05-Jan-17 104 3 .
06-Jan-17 104 3 70

 

Still the missing values are there. The missing values are expected to be filled with latest value, if older value is not available. 

Regards,

Swain
Frequent Contributor
Posts: 115

Re: Replacing the missing value with earlier date or later date

Posted in reply to DeepakSwain
Hi RW9,

Retain value provides the previous value. If very first value is missing and we want to fill it with the next available. How to proceed.
Regards,
Swain
Super User
Super User
Posts: 9,227

Re: Replacing the missing value with earlier date or later date

Posted in reply to DeepakSwain

In which case you need to do a couple of steps:

proc sort data=have out=temp;
  by id date;
  where val ne .;
run;
data temp (rename=(val=base));
  set temp;
  by id;
  if first.id;
run;

data want (drop=base);
  merge have want;
  by id;
  if val=. then val=base;
run;

This gets the first non-missing value, if there is one, and merges that back to your original data and replaces that value with the missing in original data.

Solution
‎11-20-2017 09:44 AM
Super User
Posts: 10,623

Re: Replacing the missing value with earlier date or later date

Posted in reply to DeepakSwain
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 175 views
  • 6 likes
  • 3 in conversation