how to re-format dates

[Cyndia]

In a dataset when there are a mixture of date formats as the following:

event          holidays

New Year     01/01/2012

New Year     01012012

New Year     112012

note the last one has all the zeros missing.

what are the methods to re-format all the dates in the column, holidays, to have a format of mm/dd/yyyy?  thanks.

[Patrick]

Below code doesn't provide a valid solution. Please refer to Linlin's post as per why.

Based on your posted data the variable 'holidays' must be a character variable containing strings which stand for a date.

What you want to do is convert such strings into a numeric value which stands for a SAS date and then apply a SAS date format on this numeric variable so that when printing this numeric value it shows up as a date string.

Use a SAS INFORMAT to read a string and convert it to a numeric value (=a SAS date informat). Use a SAS FORMAT to convert a numeric value to a string (eg. a SAS date to a date string).

data have;
  infile datalines truncover dsd;
  input event:$20. Char_Holidays :$10.;
  format Holidays mmddyy10.;
  datalines;
New Year,01/01/2012
New Year,01012012
New Year,112012
;
run;

data want;
  set have;
  format Holidays mmddyy10.;
  Holidays=input(Char_holidays,mmddyy10.);
  Standardized_Char_Holidays=put(input(Char_holidays,mmddyy10.),mmddyy10.);
run;

proc print data=want;
run;

N.B: A SAS date value are the number of days since 1/1/1960.

data _null_;

  date='01jan1960'd;

  do while(date<='01jan2010'd);

    put date= @20 date= date9.;

    date=intnx('year10',date,1,'s');

  end;

run;

[Linlin]

Hi Patrick,

Your code treats 112012 as 11/2012, it should be 01/01/2012. there is a link in this post https://communities.sas.com/message/109024#109024

, it maybe helpful.

Thanks - Linlin

[shivas]

Hi,

How about ANYDTDTE.

data have;

  infile datalines truncover dsd;

  input event:$20. Char_Holidays :ANYDTDTE.;

  format Char_Holidays mmddyy10.;

  datalines;

New Year,01/01/2012

New Year,01012012

New Year,112012

;

run;

Thanks,

Shiva

[Patrick]

Linlin you're of course right and I missed this one. Actually without knowing more about the possible datestrings to deal with I can't think of a one-fits-all solution.

Eg. 1122012  That could be 01Dec2012 or 11Feb2012.

@shivas:

Using ANYDTDTE. doesn't solve this issue. But when using ANYDTDTE. you best use also "option datestyle=..." or you might get unexpected results.