02-10-2015 04:25 PM
I am converting a value in a field that should be a date but is set to char 8, looks like this 01/16/08, so I am substringing out the mm dd and yy and then using mdy to put back in a date format. The problem is I am seeing some dates that look like 41/18/08 and the of course getting invalid argument to function MDY in my log. how do I care for invalid month value like this? I would not be suprised to see the same issue with the day value also.
02-10-2015 04:42 PM
no, I have to use the date in a calculation and when I changed it to mmddyy10. it appeared correctly in the data but would not work in my evaluation in my where clause. I have to compare that date to a specific date.
02-10-2015 04:49 PM
" it appeared correctly in the data but would not work in my evaluation in my where clause." I still don't get it, do you mind elaborating more? Maybe some code and the purpose of the code?
From what I can see, SUBSTR() + MDY can be replaced completely by INPUT(DATECHAR, MMDDYY10.) or other informat, besides, it is the correcter way of doing it.
02-10-2015 05:21 PM
Well, you will always have a problem with a date like 07/08/09. You don't really know which part of the date is which. If you are OK with letting SAS make a decision for you in that regard, you could use:
where input(datevar, anydte8.) < 'some specific date'd;
02-11-2015 01:18 AM
In case of real invalid values (as opposed to just a different ordering), you either have to check for each individual part, or you could try to tinker with SAS system options to avoid having the log swamped with messages, and check if your target value is missing after the input function with a date format. You can then filter those records out into a dataset that can be used for corrections.
Of course, the most prudent way is to harden the originating system against false inputs. I never have to fiddle with date values, since all my data comes out of the production DB/2 database, and dates are either valid, missing or 0001-01-01 which is "manually" converted to missing when reading into SAS.