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
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
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;
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
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
Is that date variable a SAS date value or character?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.