Replacing Missing Values

[Stat_1]

Hello Community,

I have a large data set that consists of a number of dates in the format yyymmdd.  Some values are missing the day portion only.  Is there one functon in SAS that can allow to replace these missing values by the last day of a given month?

Thank you.

[Kurt_Bremser]

I guess your variable is still of type char, containing a maximum of 8 characters?

So, how about this:

if length(oldvar) = 6
then do;
  newvar = input(trim(oldvar) !! '01',yymmdd8.);
  newvar = intnx('month',newvar,0,'end');
end;
else newvar = input(oldvar,yymmdd8.);

[Reeza]

Make sure to format the variable so it appears the way you want.

format new_var yymmdd8.;

[Kurt_Bremser]

---

@Reeza wrote:

Make sure to format the variable so it appears the way you want.

format new_var yymmdd8.;

---

Yeah, right. It's something that I tend to leave as an exercise for the dear reader&student 😉

[Stat_1]

Thank you!

[Reeza]

You could create a variable called flag that gets set to 1 if different and 0 otherwise. 

You would set the value in the do loop. 

[Stat_1]

Thank you. What if the last 2 digits for a day had the following ".." instead of nothing? How can I make this work? I also wanted to name the complete dates another name to be able to keep track of what is the real input, and what is corrected.

Thank you again!

[Reeza]

Then change your IF condition to check that the last two characters are ".." rather than the current condition which checks for length.

if substr(date, 7, 2) = ".." then do;

[Stat_1]

Thank you. I still get errors thougth.  The missing dates look like that 201605..

[Reeza]

Post your code and a sample of your data and expected output.

[Stat_1]

Please see the example below. Thank you.

data TEST;
input oldvar $;
if substr(oldvar, 7, 2) = ".." then do;
newvar = input(trim(oldvar) !! '01',yymmdd8.);
newvar = intnx('month',newvar,0,'end');
end;
else newvar = input(oldvar,yymmdd8.);
format newvar yymmdd8.;

datalines;
20160520
201505..
19970116
199901..
;
run;

[Reeza · Accepted solution]

The method is 

1. Identify records with last characters that are ".."

2. If 1 is true, remove periods and use input function to convert to a SAS date

3. If 1 is false, use input to convert to a SAS date

Part that was missing is remove the periods from the character variable. This is why sample data in your first post is so important. 

data TEST;
input oldvar $8.;
if substr(oldvar, 7, 2) = ".." then do;
newvar = input(substr(oldvar, 1, 6) || '01',yymmdd8.);
newvar = intnx('month',newvar,0,'end');
end;
else newvar = input(oldvar,yymmdd8.);
format newvar yymmdd8.;
datalines;
20160520
201505..
19970116
199901..
;
run;

[Stat_1]

Thank you for your help. Is there a way to keep track of dates that have been adjusted?

[data_null__]

This is a good example of substr function of the left side of equal.  Insert the non-missing date parts into a date of your choosing.  I don't know if you want to go all the way to and insert missing year but you can.

data dates;
   input cdate :$8.;
   newdate = '20160101';
   if not missing(cdate) then substr(newdate,1,length(cdate))=cdate;
   sasdate = input(newdate,yymmdd8.);
   format sasdate yymmddn.;
   cards;
20160520
201505
2016
.
;;;;
   run;

[Stat_1]

Thank you. What if I wanted to replace with the last day of given month?