I am importing very messy data in xls. or xlsx. files. One file may have multiple date formats, such as mm/yyyy, d-mmm, mmm-yy, etc. I want to get them all into the same date format, preferably mmddyy10., but if a different standardized format would work better I will take it. Using conditional logic would be easy enough to parse through the different date formats to assign the standard format. Is there a crosswalk of all excel data formats to SAS date formats/informats? For example, the following code set the date_want variable to the mmddyy10. format if the excel date format was MMM-YY or MM/YYY: data want;
format date_want mmddyy10.;
set have;
date_want = input(original_date, anydtdte.);
run; But, this didn't work on the D-MMM formats. How do I recode this format or any other custom excel date formats I may come across?
... View more