Replacing Missing Values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Replacing Missing Values

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.


Accepted Solutions
Solution
‎05-20-2016 04:13 PM
Super User
Posts: 19,151

Re: Replacing Missing Values

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;

View solution in original post


All Replies
Super User
Posts: 7,431

Re: Replacing Missing Values

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.);
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,151

Re: Replacing Missing Values

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

 

format new_var yymmdd8.;
Super User
Posts: 7,431

Re: Replacing Missing Values


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 Smiley Wink

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

Re: Replacing Missing Values

Thank you!

Super User
Posts: 19,151

Re: Replacing Missing Values

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. 

Occasional Contributor
Posts: 7

Re: Replacing Missing Values

[ Edited ]

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!

Super User
Posts: 19,151

Re: Replacing Missing Values

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;
Occasional Contributor
Posts: 7

Re: Replacing Missing Values

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

Super User
Posts: 19,151

Re: Replacing Missing Values

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

Occasional Contributor
Posts: 7

Re: Replacing Missing Values

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;

Solution
‎05-20-2016 04:13 PM
Super User
Posts: 19,151

Re: Replacing Missing Values

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;
Occasional Contributor
Posts: 7

Re: Replacing Missing Values

Thank you for your help. Is there a way to keep track of dates that have been adjusted?
Respected Advisor
Posts: 3,788

Re: Replacing Missing Values

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;

Capture.PNG

Occasional Contributor
Posts: 7

Re: Replacing Missing Values

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

☑ This topic is solved.

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

Discussion stats
  • 14 replies
  • 660 views
  • 10 likes
  • 4 in conversation