Checking Dates

[Zatere]

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?

[Reeza]

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]

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 · Accepted solution]

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]

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]

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]

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