topic Re: Replace missing value with previous value in SAS Programming

Replace missing value with previous value

gowtham112 — Mon, 25 Jul 2016 06:08:48 GMT

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

Re: Replace missing value with previous value

Kurt_Bremser — Mon, 25 Jul 2016 06:32:53 GMT

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?

Re: Replace missing value with previous value

Reeza — Mon, 25 Jul 2016 06:46:48 GMT

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

Why are the values missing?

Re: Replace missing value with previous value

ballardw — Mon, 25 Jul 2016 15:25:33 GMT

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?

Re: Replace missing value with previous value

gowtham112 — Mon, 25 Jul 2016 16:01:32 GMT

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

Re: Replace missing value with previous value

gowtham112 — Mon, 25 Jul 2016 16:07:58 GMT

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

Re: Replace missing value with previous value

gowtham112 — Mon, 25 Jul 2016 16:23:25 GMT

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.

Re: Replace missing value with previous value

ballardw — Mon, 25 Jul 2016 22:17:48 GMT

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.

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

Re: Replace missing value with previous value

Reeza — Mon, 25 Jul 2016 19:54:51 GMT

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