DATA Step, Macro, Functions and more

Replace missing value with previous value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Replace missing value with previous value

Hello,

 

I want to get each date with non-missing dates.

 

My approach is if I can replace missing dates to the previous no-missing value,  However, I'm having difficulty to replace these missing value.,

 

I appreciate any help.

 

 

data test;

input subid$ startdate enddate;

datalines;

XYZ-123-001-001  2013-04-04 2013-04-05

XYZ-123-001-001  2013-05-23 .

XYZ-123-001-001  2013-06-07 2013-06-13

XYZ-123-001-001  2013-06-09 2013-06-15

XYZ-123-001-001  2013-07-07 .

XYZ-123-001-001  2013-07-27 .

XYZ-123-001-001  2013-08-07 2013-08-13


Accepted Solutions
Solution
‎07-25-2016 12:24 PM
Super User
Posts: 7,803

Re: Replace missing value with previous value

[ Edited ]
Posted in reply to gowtham112

Are you looking for something like this?

data have;
input subid$ startdate enddate;
informat startdate enddate yymmdd10.;
format startdate enddate yymmddd10.;
datalines;
XYZ-123-001-001  2013-04-04 2013-04-05
XYZ-123-001-001  2013-05-23 .
XYZ-123-001-001  2013-06-07 2013-06-13
XYZ-123-001-001  2013-06-09 2013-06-15
XYZ-123-001-001  2013-07-07 .
XYZ-123-001-001  2013-07-27 .
XYZ-123-001-001  2013-08-07 2013-08-13
;
run;

data want;
set have;
retain keepdate;
if enddate ne .
then keepdate = enddate;
else enddate = keepdate;
drop keepdate;
run;

But it will create enddates that make no sense, so could you be a little more specific about the rules, or provide an example "want" dataset?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎07-25-2016 12:24 PM
Super User
Posts: 7,803

Re: Replace missing value with previous value

[ Edited ]
Posted in reply to gowtham112

Are you looking for something like this?

data have;
input subid$ startdate enddate;
informat startdate enddate yymmdd10.;
format startdate enddate yymmddd10.;
datalines;
XYZ-123-001-001  2013-04-04 2013-04-05
XYZ-123-001-001  2013-05-23 .
XYZ-123-001-001  2013-06-07 2013-06-13
XYZ-123-001-001  2013-06-09 2013-06-15
XYZ-123-001-001  2013-07-07 .
XYZ-123-001-001  2013-07-27 .
XYZ-123-001-001  2013-08-07 2013-08-13
;
run;

data want;
set have;
retain keepdate;
if enddate ne .
then keepdate = enddate;
else enddate = keepdate;
drop keepdate;
run;

But it will create enddates that make no sense, so could you be a little more specific about the rules, or provide an example "want" dataset?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 13

Re: Replace missing value with previous value

Posted in reply to KurtBremser

Thank you Kurtbremser ,for your valuable suggestion  actually i want to create TEAE flag inorder to do that i want end dates so  in my dataset some of end dates are missing so i want that missing dates to create..

Super User
Posts: 19,815

Re: Replace missing value with previous value

Posted in reply to gowtham112

Your end date is before your start date. I don't think that makes sense. 

Why are the values missing?

Occasional Contributor
Posts: 13

Re: Replace missing value with previous value

Thank you Reeza for your valuable suggestion actually in my data no end date was before start date.Actually i want to create TEAE flag to do so i want end
dates.. so i want to replace those missing values to create TEAE...
Super User
Posts: 11,343

Re: Replace missing value with previous value

Posted in reply to gowtham112

Something else to consider: What if the first record for a subject is missing the enddate? Your basic rule would be using the enddate from another subject.

 

You might consider adding a fixed number of days or advancing to end of month or similar rule for missing enddates.

 

And do you have any missing start dates?

Occasional Contributor
Posts: 13

Re: Replace missing value with previous value

Thank you Ballardw for your suggestion, In my data there were no missing start dates ,Can you suggest me how to replace end date with end of month.
Super User
Posts: 11,343

Re: Replace missing value with previous value

[ Edited ]
Posted in reply to gowtham112

Not the only way but

 

if missing enddate then enddate= intnx('month',startdate,1)-1;

 

Will give the end day of the month assuming the date variables are actually SAS date values.

This works by the default advance a date by one month gets to the first day of the following month. Subtracting one gets one day before.

 

Or

enddate= intnx('month', startdate,0,'e');   uses the alignment 'e', for end of month, and advances 0 months(i.e. the same month).

Super User
Posts: 19,815

Re: Replace missing value with previous value

Posted in reply to gowtham112

@gowtham112 FYI - You don't need to reply to each post individually. It makes the thread very hard to read.

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 402 views
  • 1 like
  • 4 in conversation