Different date types in one variable

[sanjay1]

Hi Team,

I have a variable with diffrent date types. I want to apply one date type to all the observation in the variable.

Example :

data a;
input dd;
cards;
20180118
18jan2018
011818
;
run;

result data

18jan2018
18jan2018
18jan2018

Is there is any approach for this.

Thanks,

Sanjay

[novinosrin · Accepted solution]

data a;
input dd :$10.;
cards;
20180118
18jan2018
011818
;
run;

data want;
set a;
want=input(dd,anydtdte10.);
format want date9.;
run;

[PaigeMiller]

Use the ANYDTDTE informat.

data a;
    input dd anydtdte12.;
    format dd date7.;
    cards;
20180118
18jan2018
011818
;
run;

[gamotte]

Hello,

You can use the anydtdte informat :

data want;
keep dd;
format dd date9.;
input dd anydtdte.;
cards;
20180118
18jan2018
011818
;
run;

[PeterClemmensen]

The blog post One informat to rule them all describes the ANYDTDTE informat well.

[Kurt_Bremser]

And what should be the result of

121110

?

1. December 11, 2010
2. November 12, 2010
3. November 10, 2012

Just to illustrate that your issue is non-solvable at the core. Return such inconsistent data to sender, unless sender provides a complete ruleset that covers all cases present in the files.

[ballardw]

---

@Kurt_Bremser wrote:

And what should be the result of

121110

?

1. December 11, 2010
2. November 12, 2010
3. November 10, 2012

Just to illustrate that your issue is non-solvable at the core. Return such inconsistent data to sender, unless sender provides a complete ruleset that covers all cases present in the files.

---

And do not forget the possibility of years 1910 or 1912.

[gamotte]

If the time span does not exceed a hundred years, this can be dealt with using YEARCUTOFF option.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000197881.htm

[ballardw]

---

@gamotte wrote:
If the time span does not exceed a hundred years, this can be dealt with using YEARCUTOFF option.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000197881.htm

---

I understand and have used the year cutoff values for a number of projects as I deal with dates of birth that precede 1920 (by more that a bit). The point was that a 6-digit "date" is very problematic for a large range of values. And the yearcutoff may be very off for these 6-digit values as the cutoff is likely applied to the last 2 digits and if the year were actually in the first 2 the result is suspect at best.

We didn't even go into some of the year and month only formats we have had people try to use. 200302 could be 20 Mar 2002 or 1902, or Feb 2003 with no specific day of the month.

Some of the potential date values generated by ANYDTDTE informat may be incorrect or return missing and the user will need to supply the rules for handling the problematic values.

And then there are Julian dates such as 2012. (12Jan2002).

[gamotte]

As explained in @PeterClemmensen's link the DATESTYLE option can be used to

handle such ambiguities

%macro inputDate(ds);

option DATESTYLE="&ds.";

data have_&ds.;
format dd date9.;
dd=input("121110",anydtdte.);
run;

%mend;

%inputDate(MDY);
%inputDate(DMY);
%inputDate(YMD);

[Kurt_Bremser]

With such crappy "data", you can't be sure that YMD/DMY/MDY is consistent.