topic Re: how to re-format dates in SAS Data Science

how to re-format dates

Cyndia — Fri, 20 Apr 2012 22:58:38 GMT

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.

Re: how to re-format dates

Patrick — Sat, 21 Apr 2012 02:06:15 GMT

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;

Re: how to re-format dates

Linlin — Sat, 21 Apr 2012 13:16:12 GMT

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

Re: how to re-format dates

shivas — Sat, 21 Apr 2012 16:24:12 GMT

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

Re: how to re-format dates

Patrick — Sun, 22 Apr 2012 00:49:48 GMT

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.