I am having trouble trying to format dates with single-digit days (no leading zero). The date in the dataset, in particular, corresponds to February 1st, 2000.
**EDIT** rookie mistake. I forgot to check my log and thought anydtdtew gave an actual output.
data want;
format date mmddyy10.;
informat date anydtdte.;
INPUT date;
datalines;
0212000
;
Running this code gets me an incorrect date:
06/08/2540
The following output I want is in the format mmddyy10. :
02/01/2000
So I believe the problem lies in the informat anydtdte., however, I am not sure which informat to use. I've tried other types of informats (mmddyy7.) but I can't seem to find the right one.
Thanks.
** Solved using CAT and SUBSTR functions to transform the date.
I checked here http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002605538.htm
Yes i think your informat is incorrect.
You might need to come up with data manipulation to address this one. Say read the value as character, check the length, extract using substr day, month and year then use mdy function to put the details back together. I think @Reeza mentioned something about which digit will be affected with no preceeding zero.
Can you always guarantee that the month will be 2 digit and the year will be a 4 digit?
EDIT: Please make sure your code is debugged for other errors when posting. Your INFORMAT is not correct and then SAS defaults to reading the date as a number and then using that number as a date.
Yes, all the months are 2 digits, and the years are all 4 digits.
Running with the informat anydtdte. gives me a missing value '.'
I forgot to check my log when running my program. Thanks for reminding me, I still have a lot to learn!
I tried your code and it gave me an error.
ERROR: Informat ANYDTDTEW was not found.....
Removing the "w" in anydtdtew has no error but missing value.
Make it a habit to CHECK the log (LATEST).
I checked here http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002605538.htm
Yes i think your informat is incorrect.
You might need to come up with data manipulation to address this one. Say read the value as character, check the length, extract using substr day, month and year then use mdy function to put the details back together. I think @Reeza mentioned something about which digit will be affected with no preceeding zero.
I ended up doing some data manipulation using CAT and SUBSTR functions and found a solution!
Basically I transformed 0212000 to 02/1/2000 and was able to call an informat which got it to my desired output!
Thanks, I think a light bulb popped into my head while reading the comments.
I am glad you were able to visualize what we all mentioned.
Not checking the log is not limited to "rookie mistake" (but yeah it's frequent among rookies).
Just remember when you check or review the log contents:
1. Clear previous log contents first.
2. Re-submit your code.
3. Check or review the log from top to bottom.
4. Always correct or resolve the first error (and not the latest or bottom error). Remember sas reads sequentially unless you have goto in your script (which i seldom see now).
5. Do steps 1-3 again.
Lastly, an error-free log does not mean your output is correct. A good example is that removing the "w" has no error but with missing value.
The ANYDTDTE. informat cannot handle those 7 digit strings since there is no good way to tell where the missing digit should be added.
But you should be able to use the new REGEXE features of PROC FORMAT to define your own format.
Let's call this new informat MDYYYY to emphasize that it assumes there are always 4 digit years and that Month comes before Day.
I have added a rule to assume that if the string has only 6 digits then assume that it has both a one digit month and a one digit day.
invalue mdyyyy (default=8)
's/([01][0-9])([0-9]{5}?)/${1}0$2/' (regexpe) = [mmddyy8.]
's/(\d)([0-9]{5}?)/0${1}0$2/' (regexpe) = [mmddyy8.]
'/[0-9]{8}?/' (regexp) = [mmddyy8.]
other = _error_
;
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.