topic Re: Different date types in one variable in SAS Programming

Different date types in one variable

sanjay1 — Fri, 04 Jan 2019 13:48:18 GMT

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

Re: Different date types in one variable

novinosrin — Fri, 04 Jan 2019 13:58:08 GMT

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

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

Re: Different date types in one variable

PaigeMiller — Fri, 04 Jan 2019 13:59:03 GMT

Use the ANYDTDTE informat.

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

Re: Different date types in one variable

gamotte — Fri, 04 Jan 2019 14:00:41 GMT

Hello,

You can use the anydtdte informat :

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

Re: Different date types in one variable

PeterClemmensen — Fri, 04 Jan 2019 14:21:37 GMT

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

Re: Different date types in one variable

Kurt_Bremser — Fri, 04 Jan 2019 15:01:19 GMT

And what should be the result of

December 11, 2010
November 12, 2010
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.

Re: Different date types in one variable

ballardw — Fri, 04 Jan 2019 15:06:05 GMT

@Kurt_Bremser wrote:

And what should be the result of
121110
?

December 11, 2010

November 12, 2010

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.

Re: Different date types in one variable

gamotte — Fri, 04 Jan 2019 15:51:38 GMT

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

Re: Different date types in one variable

gamotte — Fri, 04 Jan 2019 16:15:59 GMT

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

Re: Different date types in one variable

ballardw — Fri, 04 Jan 2019 17:15:38 GMT

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

Re: Different date types in one variable

Kurt_Bremser — Fri, 04 Jan 2019 18:48:48 GMT

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