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.
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;
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
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.