facing date issue

[aanan1417]

i have excel file that contain variable like name age date.

and every date is in different format.

i import this date as character and now the problem is i dont no how to get date in one format.

data have;

input name age date;

datalines;

rahul 66 03dec2020

rohit  55 2020nov17

nisha 44 12/10/2020

;

run; 

data want;

input name age date;

datalines;

rahul 66 03dec2020

rohit  55 17nov2020

nisha 44 10dec2020

;

run; 

[andreas_lds]

Please fix the obvious errors in the data step.

EDIT 1:

Some questions:

• Is it sure, that in dates like "12/10/2020" the second part (10) is always the day an never the month of the date?
• All "dates" always have day, month and year?

EDIT 2:

Working with the data you have posted:

data want;
   set have(rename=(date = str_date));

   attrib date length=8 format=date9.;

   date = input(str_date, ?? anydtdte.);

   if missing(date) then do;
      date = input(str_date, ?? mmddyy10.);
   end;

   if missing(date) then do;
      put "ERROR: Can't convert date in obs " _n_;
      put _all_;
   end;
run;

[ballardw]

First comment: make sure that shown data step code runs. Yours has errors (fixed).

data have;
input name :$10. age date :$15.;
datalines;
rahul 66 03dec2020
rohit  55 2020nov17
nisha 44 12/10/2020
;

data want (rename=(newdate=date));
  set have;
  newdate = input(date,anydtdte32.);
  format newdate date9.;
  drop date;
run;
 

The Anydtdte (any date ) informat will read many but not all common date formats. Problems typically come when using two-digit years as then a value like 01/02/03 can't tell which is actually the year (or day of the month or month in this case).

[Kurt_Bremser · Accepted solution]

And the "ANY" informats will come to different results depending on locale. A date shown as xx/xx/xxxx can be MDY  (e.g. USA) or DMY (Europe, Australia).

[aanan1417]

Thanks a ton