BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Stat_1
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

14 REPLIES 14
Kurt_Bremser
Super User

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
Super User

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

 

format new_var yymmdd8.;
Kurt_Bremser
Super User

@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
Fluorite | Level 6

Thank you!

Reeza
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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

Reeza
Super User

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

Stat_1
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6
Thank you for your help. Is there a way to keep track of dates that have been adjusted?
data_null__
Jade | Level 19

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

Stat_1
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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