topic Checking Dates in SAS Procedures

Checking Dates

Zatere — Sat, 22 Nov 2014 17:12:05 GMT

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?

Re: Checking Dates

Reeza — Sat, 22 Nov 2014 19:04:05 GMT

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.

Re: Checking Dates

Zatere — Sat, 22 Nov 2014 20:51:41 GMT

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.

Re: Checking Dates

Reeza — Sun, 23 Nov 2014 03:00:52 GMT

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;

Re: Checking Dates

Ksharp — Sun, 23 Nov 2014 08:57:48 GMT

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

Re: Checking Dates

Zatere — Sun, 23 Nov 2014 14:04:15 GMT

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?

Re: Checking Dates

Reeza — Sun, 23 Nov 2014 23:56:11 GMT

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.