How to read different date format and convert into sas date format

Reply
Contributor
Posts: 32

How to read different date format and convert into sas date format

Hi,

Could any one help me out, how to read different date  format,

I have an dataset like,


NameAddmission date
Ram28/01/1987
Sabari18-Aug-1996
Kalyan28-01-97
Vijay121987
Mervin1990
Yesu01UU1980
NeerajUU011987


  I want Admission date to be in sas date format or Any sas dateformat and i also want the missing day as 07 and missing months as 01

I want dataset to be as

NameDate
Ram28-01-1987
Sabari18-08-1996
Kalyan28-01-1997
Vijay07-12-1987
Mervin07-01-1990
Yesu07-01-1980
Neeraj07-01-1987
Super Contributor
Posts: 297

Re: How to read different date format and convert into sas date format

Posted in reply to SushilKumar

Is this coming from an external file?

SAS Super FREQ
Posts: 708

Re: How to read different date format and convert into sas date format

Posted in reply to SushilKumar

Hi SushilKumar

The ANYDTDTE. informat read many different date representations, but some are not ready in as a date. So you will need some logic to adapt the text so that the ANYDTDTE. informat can read it as a date.

Find below an example that uses regular expressions to check for some pattern, but not all the cases. But you can addtional logic to handle the other cases as well:

data want;
  infile cards dlm=",";
 
input
    name :
$32.
    admissionDate_c :
$32.
  ;
  select;
   
when ( prxmatch('/^\d{4}$/', trim(admissionDate_c) ) = 1) do;
      admissionDate_c2 = cats(
"0701", admissionDate_c);
    end;
   
when ( prxmatch('/^\d{6}$/', trim(admissionDate_c) ) = 1) do;
      admissionDate_c2 = cats(
"07", admissionDate_c);
    end;
    
   
otherwise do;
       admissionDate_c2 = admissionDate_c;
   
end;
 
end;

  admissionDate = input(admissionDate_c2,
anydtdte32.);
  format
    admissionDate
date9.
  ;
  cards;
Ram,28/01/1987
Sabari,18-Aug-1996
Kalyan,28-01-97
Vijay,121987
Mervin,1990
Yesu,01UU1980
Neeraj,UU011987
;
Contributor
Posts: 32

Re: How to read different date format and convert into sas date format

Posted in reply to Bruno_SAS

Thank you so much

Contributor
Posts: 32

Re: How to read different date format and convert into sas date format

Posted in reply to Bruno_SAS

Thank you so much

Super Contributor
Posts: 644

Re: How to read different date format and convert into sas date format

Posted in reply to SushilKumar

The ANYDTDTEw. informat might provide a means of interpreting some of these dates but you will have a problem with ambiguity:  121987 might be interpreted as 01-02-1987 or 02-10-1987 depending on locale; neither is what you want. 

You will have to clean up your data before you can input it to insert default values.

If the following are always true you may have a chance:

* "UU" (or nothing) is always used to indicate an unknown day or month

* Admission year must not be missing and must always be the last (or only) value given

* A six digit value would be MMYYYY or DDMMYY

The suggested strategy would be to identify records with day and/or month missing and substitute the default values.  Using 'adm' as shorthand for admission date, try

  Select ;

  When (length(adm) = 2)

  adm = cats ('07-01-', adm) ;

    When (length(adm) = 4 and adm = compress (adm, ' ', 'KD'))

  do ;

  If 1920 < input(adm, 4.) < 2020

  then adm = cats ('07-01-', adm) ;

  else adm = catx ('-','07', substr(adm, 1, 2), substr(adm, 3, 2)) ;

  end ;

    When (length(adm) = 6 and adm = compress (adm, ' ', 'KD'))

  do ;

  If 1920 < input(substr(adm, 3, 4), 4.) < 2020

  then adm = catx ('-','07', substr(adm, 1, 2), substr(adm, 3, 4)) ;

  /* else leave adm unchanged, in DDMMYY format */

  end ;

  When (index (upcase(adm), 'U') > 0)

  do ;

  If index (upcase(adm), 'U') = 1

      then adm = cats ('07', substr (adm, 3, length(adm))) ;

  If index (upcase(adm), 'U') > 1

      then adm = transtr (upcase (adm), 'UU', '07') ;

      end ;

  Otherwise ;

end ;

date = input (adm, ?? ANYDTDTE.) ;

format date ddmmyy10. ;

if date = .

  then put 'Record ' _N_ adm= ; 

(Untested code)

Code should write any values that cannot be interpreted correctly to the log, so these can be examined and either edited manually (if only a few) or additional statements added to the Select group.

Richard

Ask a Question
Discussion stats
  • 5 replies
  • 479 views
  • 0 likes
  • 4 in conversation