sas >> date: mmddyyyy with no separators or leading zeros

by Bill McKirgan » Fri, 25 Jan 2008 00:17:25 GMT

How does one read MMDDYYYY dates where there are no separators like
dashes or slashes or leading zeros to preserve the positional MM DD
reference?

Here's an example of what I'm trying to work with:


data whatdates;
input end_of_mbrship_term ;
cards;
8312007
12312007
5312008
112008
run;


data dates; set whatdates;

termdate= input ( end_of_mbrship_term , mmddyy10. );

put end_of_mbrship_term /
termdate mmddyy10. //
;

run;




I'm thinking I should just turn this into a string, and extract the
year, month and date that way.

Year is fairly easy...reverse take the first 4 columns and reverse
again

But how to get the rest?

And how to know what is the month and what is the date?

I searched the L on this and found nothing. Please let me know if you
have any solutions or links to where this is covered.

Thanks,

Bill McKirgan


sas >> date: mmddyyyy with no separators or leading zeros

by Virtual SUG » Fri, 25 Jan 2008 00:32:03 GMT


I think that perhaps the easiest approach is to use the MMDDYY10
informat in your INPUT statement to have SAS convert the raw data
values to SAS date values (i.e., the number of days from Jan 1,
1960). This should avoid a lot of otherwise unnecessary and tedious
data step coding.

Try this:

data whatdates;
input end_of_mbrship_term mmddyy10.;
format end_of_mbrship_term mmddyy10.;
cards;
8312007
12312007
5312008
112008
run;

proc print;
run;

Hope this helps!

Andrew Karp
Sierra Information Services
www.SierraInformation.com







> data dates; set whatdates; >> >> termdate= input ( end_of_mbrship_term , mmddyy10. );> >> > put end_of_mbrship_term >
> termdate mmddyy10. >//
>>> ;
>
>
> I'm thinking I should just turn this into a string, and extra>t the
> year, month and date tha> w>y.
>
> Year is fairly easy...reverse take the first 4 columns and r>verse
>>ag>in
>
> But how to get the>re>t?
>
> And how to know what is the month and what is the>da>e?
>
> I searched the L on this and found nothing. lease let me know >f you
> have any solutions or links to where this is co>er>d.
>
> T>an>s,
>
> Bill McKirgan




sas >> date: mmddyyyy with no separators or leading zeros

by NordlDJ » Fri, 25 Jan 2008 00:48:51 GMT

> -----Original Message-----

Bill,

Andrew Karp has given you a good suggestion about using a date informat for reading you dates. Just be aware that SAS will interpret dates as best it can based on what it finds. If your dates will sometimes not have a leading 0 for single digit months or days, then you might not get what you want. For example, is your last example date (i.e. 112008) 01Jan2008 or 20Nov2008 (which is what SAS will return)? This is not just a formatting issue. This will also be a problem with any string manipulation routines which you might code up.

Dan

Daniel J. Nordlund
Research and Data Analysis
Washington State Department of Social and Health Services
Olympia, WA 98504-5204


date: mmddyyyy with no separators or leading zeros

by JClark » Fri, 25 Jan 2008 00:56:16 GMT

Andrew,

How would that informat determine whether the string 1212008 should be formatted as 01/21/2008 or 12/01/2008?


Jack Clark
Research Analyst
Center for Health Program Development and Management
University of Maryland, Baltimore County

-----Original Message-----
From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of Virtual SUG
Sent: Thursday, January 24, 2008 11:32 AM
To: XXXX@XXXXX.COM
Subject: Re: date: mmddyyyy with no separators or leading zeros

I think that perhaps the easiest approach is to use the MMDDYY10
informat in your INPUT statement to have SAS convert the raw data
values to SAS date values (i.e., the number of days from Jan 1,
1960). This should avoid a lot of otherwise unnecessary and tedious
data step coding.

Try this:

data whatdates;
input end_of_mbrship_term mmddyy10.;
format end_of_mbrship_term mmddyy10.;
cards;
8312007
12312007
5312008
112008
run;

proc print;
run;

Hope this helps!

Andrew Karp
Sierra Information Services
www.SierraInformation.com







> data dates; set whatdates; >> >> termdate= input ( end_of_mbrship_term , mmddyy10. );> >> > put end_of_mbrship_term >
> termdate mmddyy10. >//
>>> ;
>
>
> I'm thinking I should just turn this into a string, and extra>t the
> year, month and date tha> w>y.
>
> Year is fairly easy...reverse take the first 4 columns and r>verse
>>ag>in
>
> But how to get the>re>t?
>
> And how to know what is the month and what is the>da>e?
>
> I searched the L on this and found nothing. lease let me know >f you
> have any solutions or links to where this is co>er>d.
>
> T>an>s,
>
> Bill McKirgan


date: mmddyyyy with no separators or leading zeros

by Bill McKirgan » Fri, 25 Jan 2008 01:05:20 GMT

Andrew,

Thanks for taking a look at this and posting a solution. I did try
reading the date using the mmddyy10. informat/format but it failed on
dates that are 6 columns long, like '112008'. This example should be
1/1/2008, but becomes 11/20/2008 after being read with the format.

I've taken another approach that I have doubts about, and that is to
parse the string out by using reverse functions and lengthN function
to determine non-blank string length. I can reliably read the 8-
column dates and the 6-column dates. The 7-column dates are what I
still have trouble with. I need to setup a way to evaluate which two
of the three digits are dates and/or months.

Thanks to you and others on the L for any thoughts, comments or
suggstions.

-- Bill McKirgan




data whatdates;
input end_of_mbrship_term ;
cards;
8312007
12312007
1212008
5312008
112008
1122008
;
run;

data dates; set whatdates;
datestr = put(end_of_mbrship_term,$8.);
howlong = lengthN(left(datestr));

year= reverse(substr(reverse(datestr),1,4));
mody= substr(left(datestr),1,4);

if howlong = 8 then do;
mo= input(substr(left(mody),1,2),best8.);
dy= input(substr(left(mody),3,2),best8.);
end;

/* here I need to determine which is single-digit
the month or the date ?
*/
if howlong = 7 then do;
mo= input(substr(left(mody),1,1),best8.);
dy= input(substr(left(mody),2,2),best8.);
end;

if howlong = 6 then do;
mo= input(substr(left(mody),1,1),best8.);
dy= input(substr(left(mody),1,1),best8.);
end;


termdate=mdy(mo,dy,year); format termdate date9.;

put end_of_mbrship_term /
howlong /
year /
mody /
mo /
dy /
termdate //
;
run;





-- log output --


893 put end_of_mbrship_term /
894 howlong /
895 year /
896 mody /
897 mo /
898 dy /
899 termdate //
900 ;
901 run;

NOTE: Character values have been converted to numeric
values at the places given by: (Line):(Column).
891:20
8312007
7
2007
8312
8
31
31AUG2007


12312007
8
2007
1231
12
31
31DEC2007


1212008
7
2008
1212
1
21
21JAN2008 < ------ Badness


5312008
7
2008
5312
5
31
31MAY2008


112008
6
2008
1120
1
1
01JAN2008


1122008
7
2008
1122
1
12
12JAN2008
NOTE: There were 6 observations read from the data set WORK.WHATDATES.
NOTE: The data set WORK.DATES has 6 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds






date: mmddyyyy with no separators or leading zeros

by Bill McKirgan » Fri, 25 Jan 2008 01:17:10 GMT

Dan and Jack have identified the crux if my problem reading these
awful date values.

I have just posted a partial solution on fishing the year month and
date values from a non-zero filled unformatted numeric variable that
represents date. It's easy to get the 8-column dates and 6-column
dates as the rules are simple.

If 8-cols then the first two are a 2-digit month, the second two are a
2-digit date, and the last 4 is the 4-digit year.
If 6-cols then the first is a 1-digit month, the second column is a 1-
digit date, and the last 4 is the 4-digit year.

If 7 columns then we have a condition where the first three columns
represent month and date.
How to determine if column 1 is a single digit month?

This is where my mind is going to mush...

Read column 1 as a single-digit month and see if the 2-digit date left
over violates any date rules (>29 if mo=2, etc)

and vice versa

Read column 2 as single-digit date and look for 2-digit months that
violate rules like (month > 12).


This is a problem I usually solve by asking the data provider for
better data or
by re-keying the data or
by doing nothing with the data

I need to do something with it, it is largish, and if I can craft a
good solution here it will be here on the L for others who may need
such a solution.



-- Bill McKirgan



date: mmddyyyy with no separators or leading zeros

by NordlDJ » Fri, 25 Jan 2008 01:25:41 GMT

> -----Original Message-----

Bill,

Without separators, or always using leading zeros with single digit days and months, you are up the proverbial creek without a paddle. In a some cases you can make a decision base on valid month and day values (e.g. 1912008), but there is no way to decide all cases based on the date string alone. What date is 1232008 ?

Dan

Daniel J. Nordlund
Research and Data Analysis
Washington State Department of Social and Health Services
Olympia, WA 98504-5204


date: mmddyyyy with no separators or leading zeros

by Nathaniel.Wooding » Fri, 25 Jan 2008 02:00:24 GMT

ill

Here is a slightly different parsing solution that assumes that a day less
than 10 will always have a leading zero. Unless there is some other
information at hand, I do not see how you can determine whether 1122008 is
Jan 12 vs Nov 2.

data whatdates;
input rawdate $8.;
length longdate $ 8;
if length(rawdate) lt 8 then
longdate=compress('0'||rawdate);
else longdate=rawdate;
if length(longdate) = 8 then date = input(longdate,mmddyy10.);
else do;
month = substr(rawdate,1,1);
day = substr(rawdate,2,1);
part2 = substr(rawdate,3);
longdate = compress('0'||month||'0'||day||part2);
date = input(longdate,mmddyy10.);
end;
drop part2 month day longdate rawdate;
format date mmddyy10.;
cards;
8312007
12312007
1212008
5312008
112008
1122008
;
run;
proc print;
run;


I understand that a Mr. Anthony Soprano of New Jersey has left his old job
and is looking for work. I suggest that you hire him to be your emissary
and have him deliver your sincere thanks to who ever coded this mess.

Nat

Nat Wooding
Environmental Specialist III
Dominion, Environmental Biology
4111 Castlewood Rd
Richmond, VA 23234
Phone:804-271-5313, Fax: 804-271-2977



Bill McKirgan
<Bill.McKirgan@GM
AIL.COM> To
Sent by: "SAS(r) XXXX@XXXXX.COM
Discussion" cc
< XXXX@XXXXX.COM
GA.EDU> Subject
Re: date: mmddyyyy with no
separators or leading zeros
01/24/2008 12:05
PM


Please respond to
Bill McKirgan
<Bill.McKirgan@GM
AIL.COM>






Andrew,

Thanks for taking a look at this and posting a solution. I did try
reading the date using the mmddyy10. informat/format but it failed on
dates that are 6 columns long, like '112008'. This example should be
1/1/2008, but becomes 11/20/2008 after being read with the format.

I've taken another approach that I have doubts about, and that is to
parse the string out by using reverse functions and lengthN function
to determine non-blank string length. I can reliably read the 8-
column dates and the 6-column dates. The 7-column dates are what I
still have trouble with. I need to setup a way to evaluate which two
of the three digits are dates and/or months.

Thanks to you and others on the L for any thoughts, comments or
suggstions.

-- Bill McKirgan




data whatdates;
input end_of_mbrship_term ;
cards;
8312007
12312007
1212008
5312008
112008
1122008
;
run;

data dates; set whatdates;
datestr = put(end_of_mbrship_term,$8.);
howlong = lengthN(left(datestr));

year= reverse(substr(reverse(datestr),1,4));
mody= substr(left(datestr),1,4);

if howlong = 8 then do;
mo= input(substr(left(mody),1,2),best8.);
dy= input(substr(left(mody),3,2),best8.);
end;

/* here I need to determine which is single-digit
the month or the date ?
*/
if howlong = 7 then do;
mo= input(sub

date: mmddyyyy with no separators or leading zeros

by Bill McKirgan » Fri, 25 Jan 2008 03:44:57 GMT

Dan and Nat,

Thanks for pointing to the true crux of the problem...once dates are
reduced like this we can only recover part of the information. Some
if it will always be in doubt and will require more information.

Thanks for your example Nat. I will try that out. I'm sure that I
can use that on my current problem, and will try to flag the dates
that could be in doubt and check them individually.

-- Bill McKirgan


date: mmddyyyy with no separators or leading zeros

by barry.a.schwarz » Tue, 29 Jan 2008 19:41:25 GMT

Since the provider of your data sees no need to remove the ambiguity,
obviously he is not concerned about the accuracy of the results. Tell
him he will get whatever mmddyy10. thinks is appropriate and move on to
the real processing issues.

-----Original Message-----
From: Bill McKirgan [mailto:snip]
Sent: Thursday, January 24, 2008 9:17 AM
To: XXXX@XXXXX.COM
Subject: Re: date: mmddyyyy with no separators or leading zeros

Dan and Jack have identified the crux if my problem reading these awful
date values.

I have just posted a partial solution on fishing the year month and date
values from a non-zero filled unformatted numeric variable that
represents date. It's easy to get the 8-column dates and 6-column dates
as the rules are simple.

If 8-cols then the first two are a 2-digit month, the second two are a
2-digit date, and the last 4 is the 4-digit year.
If 6-cols then the first is a 1-digit month, the second column is a 1-
digit date, and the last 4 is the 4-digit year.

If 7 columns then we have a condition where the first three columns
represent month and date.
How to determine if column 1 is a single digit month?

This is where my mind is going to mush...

Read column 1 as a single-digit month and see if the 2-digit date left
over violates any date rules (>29 if mo=2, etc)

and vice versa

Read column 2 as single-digit date and look for 2-digit months that
violate rules like (month > 12).


This is a problem I usually solve by asking the data provider for better
data or by re-keying the data or by doing nothing with the data

I need to do something with it, it is largish, and if I can craft a good
solution here it will be here on the L for others who may need such a
solution.



-- Bill McKirgan


Similar Threads

1. mmddyyyy date value without the slashes

2. Excel Export keep leading zeros

Hello,

=20

I'm creating a excel file using ODS and I am trying to maintain leading
zeros in one of the fields.  I created it as a character field in SAS
but in excel it reads it as numeric and cuts off the leading zeros.

=20

Any suggestions?

=20

Thanks in advance,

=20

Jessica

3. One liner to convert numeric ssn to 9-digit character variable with leading zeros

4. Reading in Numeric Data with Leading Zeros

5. Adding leading zeroes to a variable

6. numeric to character, concatenate, compress, and add a leading zero

I was hoping that someone may be able to help me with this seemingly
simple exercise.

I have 2 numeric variables - county (values 1-88) and station (values
1-30).  I want to create a new variable called check_station by
concatenating the county and station.  I've used the put function to
convert the fields to character variables and I used the compress and
concatenate functions to get what I wanted - almost.  What I need help
with is the following:  When station is < 10, I want a leading zero
(0) added.

Example:

County=33; station=5

Current result:335

Desired result:3305

Any help would be greatly appreciated.

tonk

7. Leading Zeros in SAS JMP

8. Semi-OT: format .csv or .htm, import to excel, retain leading zeros