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?
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;
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.
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.
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;
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?
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.
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
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!
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.
Ready to level-up your skills? Choose your own adventure.