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

Hi,

In my Data set I have number of dates which are wrong. They appear as 01 - 31Dec9999 instead of 01 -31Dec1999.

What I want to do is to convert 9999 into 1999.

Any Ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

9999 is usually used to indicate missing data. Is it possible to have missing values? Are all the values 31Dec9999 rather than various dates? If so then I'd definitely think the dates were invalid or something else rather than 31Dec1999. If the dates vary (i.e. 15Apr9999) then its possible.

Nevertheless if you're 100% you can fix it two in a straightforward IF/THEN condition

If the data is a SAS date (numeric with date9. format in your case) then the following will work:

if year(old_date)=9999 then new_date=mdy(month(old_date), day(old_date), 1999);

else new_date=old_date;

View solution in original post

6 REPLIES 6
Reeza
Super User

Are these SAS dates or text fields?

If its a text field you can look into tranwrd searching for 9999 instead of 1999.

Also, are you sure these are incorrect. Its a common practice to code dates as 9999 to indicate a date in the distant future. The rationale for this is it makes it easier for querying date periods instead of needing to deal with null values.

Zatere
Quartz | Level 8

All dates are in a SAS Data Set. It seems that the values are incorrect, because they describe a period between 01JAN1998 and 31DEC2002.

When I checked the ranges for the dates (valid dates are only between 01JAN1998 and 31DEC2002)  I found those invalid values.

I have to either get rid of those invalid dates or to convert them into valid.

Reeza
Super User

9999 is usually used to indicate missing data. Is it possible to have missing values? Are all the values 31Dec9999 rather than various dates? If so then I'd definitely think the dates were invalid or something else rather than 31Dec1999. If the dates vary (i.e. 15Apr9999) then its possible.

Nevertheless if you're 100% you can fix it two in a straightforward IF/THEN condition

If the data is a SAS date (numeric with date9. format in your case) then the following will work:

if year(old_date)=9999 then new_date=mdy(month(old_date), day(old_date), 1999);

else new_date=old_date;

Zatere
Quartz | Level 8

Both codes are working.

I did not want to create a new column therefore I used the following:

if year(old_date)=9999 then old_date=mdy(month(old_date), day(old_date), 1999);

else old_date=old_date;


However I looked at the Data Set again and it seems that you are right because the dates do not vary; they are always 31Dec9999. The data set does not contain missing values, as per missing values check.


If those dates are invalid rather than 31Dec1999 as I thought initially, may I ask how I should proceed with that? Should I delete them?

Reeza
Super User

That depends on your subject area. Without any knowledge of the data its impossible to say. For example if this was a clinical trials process these may be people who survived past the trial date so then survival analysis method is used and those observations are censored. 

Ksharp
Super User

There is must be a very large value in data variable.

data have;
a='31Dec9999'd;
b=mdy(month(a),day(a),1999);
format a b date9.;
run;

Xia Keshan

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 3560 views
  • 0 likes
  • 3 in conversation