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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.