topic Re: Date informat with single digit days in SAS Programming

Date informat with single digit days

Ahzu — Fri, 06 Oct 2017 03:57:43 GMT

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.

Re: Date informat with single digit days

Reeza — Fri, 06 Oct 2017 03:14:14 GMT

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.

Re: Date informat with single digit days

error_prone — Fri, 06 Oct 2017 03:12:36 GMT

Removing the "w" from anydtdtew could help.

Re: Date informat with single digit days

ShiroAmada — Fri, 06 Oct 2017 03:23:00 GMT

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).

Re: Date informat with single digit days

Ahzu — Fri, 06 Oct 2017 03:34:43 GMT

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!

Re: Date informat with single digit days

Ahzu — Fri, 06 Oct 2017 03:36:43 GMT

Thanks for reminding me to check my log! I am still a beginner with SAS. But yes, I also get a missing value after removing the 'w'.

Re: Date informat with single digit days

ShiroAmada — Fri, 06 Oct 2017 03:57:32 GMT

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.

Re: Date informat with single digit days

Ahzu — Fri, 06 Oct 2017 04:01:36 GMT

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.

Re: Date informat with single digit days

ShiroAmada — Fri, 06 Oct 2017 04:06:59 GMT

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.

Re: Date informat with single digit days

Tom — Fri, 06 Oct 2017 05:16:42 GMT

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_
;