topic Re: Replacing Missing Values in SAS Programming

Replacing Missing Values

Stat_1 — Fri, 20 May 2016 05:15:48 GMT

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.

Re: Replacing Missing Values

Kurt_Bremser — Fri, 20 May 2016 06:21:46 GMT

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

Re: Replacing Missing Values

Reeza — Fri, 20 May 2016 08:51:25 GMT

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

format new_var yymmdd8.;

Re: Replacing Missing Values

Kurt_Bremser — Fri, 20 May 2016 09:03:29 GMT

@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 😉

Re: Replacing Missing Values

data_null__ — Fri, 20 May 2016 12:03:30 GMT

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;

Re: Replacing Missing Values

Stat_1 — Fri, 20 May 2016 17:32:18 GMT

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!

Re: Replacing Missing Values

Reeza — Fri, 20 May 2016 17:34:30 GMT

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;

Re: Replacing Missing Values

Stat_1 — Fri, 20 May 2016 17:41:03 GMT

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

Re: Replacing Missing Values

Reeza — Fri, 20 May 2016 17:42:41 GMT

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

Re: Replacing Missing Values

Stat_1 — Fri, 20 May 2016 17:44:00 GMT

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

Re: Replacing Missing Values

Stat_1 — Fri, 20 May 2016 18:00:12 GMT

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;

Re: Replacing Missing Values

Reeza — Fri, 20 May 2016 18:08:26 GMT

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;

Re: Replacing Missing Values

Stat_1 — Fri, 20 May 2016 19:42:08 GMT

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

Re: Replacing Missing Values

Stat_1 — Fri, 20 May 2016 20:14:22 GMT

Thank you!

Re: Replacing Missing Values

Reeza — Fri, 20 May 2016 20:22:39 GMT

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.