Learning SAS? Welcome to the exclusive online community for all SAS learners.

How to read dates that are mixed within a field such that no single date format can read them.

Reply
Occasional Contributor
Posts: 17

How to read dates that are mixed within a field such that no single date format can read them.

Hello Experts,

I am stuck at how to read the dates when we have dates in different format. I read the date as Character Date & used input function to convert to the date format but I get missing values for the 1st & 4th observation. Please help as to how should I proceed further. The example I was doing is as follows:

data sample;

     input date_char $12.;

  date = input(date_char, date9.);

  put date=;

  format date date9.;

  datalines;

01/01/2001

01Jan2001

03Jan01

04/05/01

;

run;

Super User
Super User
Posts: 7,955

Re: How to read dates that are mixed within a field such that no single date format can read them.

Posted in reply to gurpreetkaur

Hi,

There is an anydate. format which may help you there.

http://support.sas.com/resources/papers/proceedings11/117-2011.pdf

Super User
Posts: 7,785

Re: How to read dates that are mixed within a field such that no single date format can read them.

Posted in reply to gurpreetkaur

Another way is to do some analyzing and use different input statements with different formats, depending on certain markers (length, alpha characters in certain locations).

Be aware that 04/05/01 cannot be reliably solved; it could mean April 5, 2001, or May 4, 2001, or May 1, 2004.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 95

Re: How to read dates that are mixed within a field such that no single date format can read them.

Posted in reply to gurpreetkaur

I would test for a '/' in date_char.  the code below works for the limited data.

data sample;

     input date_char $12.;

     x=substr(date_char,3,1);

if x='/' then date=input(date_char, ddmmyy10.);

  else date = input(date_char, date9.);

  put date= x=;

  format date date9.;

  datalines;

01/01/2001

01Jan2001

03Jan01

04/05/01

proc print; run;

Regular Contributor
Posts: 180

Re: How to read dates that are mixed within a field such that no single date format can read them.

Posted in reply to gurpreetkaur

Try informat anydtdte. :

data sample;

     input date_char $12.;

  date = input(date_char, anydtdte12.);

  put date=;

  format date date9.;

  datalines;

01/01/2001

01Jan2001

03Jan01

04/05/01

;

run;

CTorres

Trusted Advisor
Posts: 1,137

Re: How to read dates that are mixed within a field such that no single date format can read them.

Posted in reply to gurpreetkaur

You can try another informat anydtdtm.

data sample;

     input date_char $12.;

  date = datepart(input(date_char, anydtdtm.));

  put date=;

  format date date9.;

  datalines;

01/01/2001

01Jan2001

03Jan01

04/05/01

;

run;

Thanks,

Jag

Thanks,
Jag
Ask a Question
Discussion stats
  • 5 replies
  • 948 views
  • 1 like
  • 6 in conversation