Incorrect Date in the Dataset (Reversing Date and Months)

[DavidLie]

Hi SAS Experts,

I have been given a dataset and the dates were incorrectly imported (but I don't have the raw file). So, at the moment, the dataset has the following entries:

01 JAN 2010

01 FEB 2010 (which supposed to be 02 JAN 2010)

01 MAR 2010 (which supposed to be 03 JAN 2010)

01 APR 2010

.

.

01 DEC 2010

13 JAN 2010

14 JAN 2010

15 JAN 2010

In short, the first 12 dates are incorrect as the dates and months are swapped. However, from date 13 onwards, it is correct and reads well.

So, does anyone have a solution to combat this problem?

Best,

David

[novinosrin]

I would like a better sample to know what's in there in your dataset. Anyway, see if this helps:

data want;

set have;

retain _date;

if _n_=1 then _date=date;

if _n_>1 then do;

if date ne _date+1 then date=_date+1;

_date=date;

end;

format _date  date9.;

drop _date;

run;

Regards,

Naveen Srinivasan

[Ksharp]

It seems you reverse the month and day.

data _null_;
d1='01feb2010'd;
d2=mdy(day(d1),month(d1),year(d1));
put d1= date9. d2= date9.;
run;

[Peter_C]

data better ;
set have ;
 if day( date) <= 12 then date = mdy( day( date), month( date), year( date) ) ;
run ;

This would implement the general fix where informat anydtdte. has used the wrong default order

[art297]

If your dates are already SAS dates, then others have already provided the solution you're looking for. However, the way you showed them, they look like they are in a character field. If so, I'd suggest trying something like:

data have;
  informat date $11.;
  input date &;
  cards;
01 JAN 2010
01 FEB 2010
01 MAR 2010
01 APR 2010
01 MAY 2010
01 JUN 2010
01 JUL 2010
01 AUG 2010
01 SEP 2010
01 OCT 2010
01 NOV 2010
01 DEC 2010
13 JAN 2010
14 JAN 2010
;

data want (drop=_:);
  format date date9.;
  set have (rename=(date=_date));
  date=input(_date,anydtdte11.);
  if _n_ le 12 then date=mdy(day(date),month(date),year(date));
run;

Art, CEO, AnalystFinder.com

[ballardw]

Is that date variable a SAS date value or character?