topic Re: date formats in SAS Programming

date formats

molla — Wed, 21 Dec 2016 07:17:29 GMT

Hi I have following data ,

dates

13APR1999

120499

2016-11-19

11-18-2016

2016

un-18-2018

un-unk-2016

I need to get all these dated to yymmdd10. format,

Re: date formats

chrej5am — Wed, 21 Dec 2016 07:29:13 GMT

what should be the output for

2016

un-18-2018

un-unk-2016

Re: date formats

molla — Wed, 21 Dec 2016 07:36:54 GMT

for 2106 the output should be 2016

for

un-18-2018 2016-18-

un-unk-2016 2016- -

there should be blank in the place of un and unk

Re: date formats

Shmuel — Wed, 21 Dec 2016 07:54:29 GMT

If do not intend to convert the input into sas date variable then just read it a

alphanumeric and replace the un/unk inro space:

data want;

infile datalines truncover;

input datex $10.;

datex = tranword(datex, ' ','un');

datex = translate(datex,' ','k');

datalines;

13APR1999

120499

2016-11-19

11-18-2016

2016

un-18-2018

un-unk-2016

; run;

Re: date formats

molla — Wed, 21 Dec 2016 07:57:02 GMT

I need to convert that to sas date varaible and display in yymmdd10.format

Re: date formats

chrej5am — Wed, 21 Dec 2016 07:59:16 GMT

You can not have this kind of output in the column with ordinary yymmdd10. format.

Either construct your own format

extract Year Month and Date from your input and based on availability of these values construct date column formatted as yymmdd10.

Re: date formats

Shmuel — Wed, 21 Dec 2016 08:03:36 GMT

how would you interpre input like: 09-05-2015

is it a ddmmyy10. informat or mmddyy10. informat ?

and what about: 120499 - is ir 12apr1999 or 12apr2099 or 04dec1999 etc?

You cannot have a unque result unless you have some more restrictions.

Re: date formats

molla — Wed, 21 Dec 2016 08:05:15 GMT

ok..

In the case of 13APR1999 input, can I compare the variable with sas datesets and convert them to yymmdd10. format, like

if datetest=date9. then

d1=input(datetest,yymmdd10.)

In these way can i compare the varable with every sas date format and convert them to yyddmm10. format

Re: date formats

Kurt_Bremser — Wed, 21 Dec 2016 08:08:36 GMT

@molla wrote:

for 2106 the output should be 2016

for

un-18-2018 2016-18-

un-unk-2016 2016- -

there should be blank in the place of un and unk

You can`t do that with the standard YYMMDD10. format; it needs a date value as input, and will always display days and months.

You will have to keep year, month and day in different variables so you can store the un/unk as missing values there, while preserving the non-missing year (or month).

BTW, there is no 18th month in the Gregorian calendar 😉

Re: date formats

molla — Wed, 21 Dec 2016 08:09:38 GMT

for all the possible sas date informats i need to convert to yymmdd10. format,there I have given only few date informats,

Re: date formats

molla — Wed, 21 Dec 2016 08:13:34 GMT

My requirement is consider a varaible which contain all the possible date informats of sas ,some of the months ,years are missing in the observations ,I need to convert all those to a common standard frmat yymdd10.

Re: date formats

molla — Wed, 21 Dec 2016 08:15:33 GMT

here 18 is not month it is date, it id in mmddyy10. format

Re: date formats

Kurt_Bremser — Wed, 21 Dec 2016 08:18:49 GMT

@molla wrote:

My requirement is consider a varaible which contain all the possible date informats of sas ,some of the months ,years are missing in the observations ,I need to convert all those to a common standard frmat yymdd10.

This is only possible if you keep year, month and day in separate variables. A SAS date value (just like a date value in any programming environment) is a count of days from a certain offset (1960-01-01 in the case of SAS) and will always contain a valid month and day.

Re: date formats

Reeza — Wed, 21 Dec 2016 08:18:57 GMT

Do you know the informat ahead of time?

Or are you deriving it based on the appearance of the data?

As mentioned, mmddyy and ddmmyy are both valid informats that cannot be uniquely identified.

If you don't know the format this may not be possible.

The SAS catch all informat is ANYDTDTE.

For your exact data, please post the expected results.

Re: date formats

Shmuel — Wed, 21 Dec 2016 08:48:31 GMT

Here is a code to convert your sample into sas dates.

data test;
infile datalines truncover;
input datex $12.; /* anydate10.; */
datex = lowcase(datex);

/* define your preferenced order of trial */
if input(datex,?? date9.) > 0
then date = input(datex,date9.); else
if input(datex,?? date7.) > 0
then date = input(datex,date9.); else
if input(datex,?? ddmmyy10.) > 0
then date = input(datex,ddmmyy10.); else
if input(datex,?? yymmdd10.) > 0
then date = input(datex,yymmdd10.);
else do;
datex = translate(datex,' ','unk');
do i=1 to 3;
num = scan(datex,i,'-/');
if (0 < num le 12) then mm=num; else
if (0 < num le 31) then dd=num; else
if num le 99 or num > 1900 then yy=num; /* year 2 or 4 digits */
end;
if yy =. then yy = input(datex, ?? best11.);
if dd le 0 then dd=01;
if mm le 0 then mm=01;
date = mdy(mm,dd,yy);
end;

format date ddmmyy10.;

datalines;
13apr1999
120499
2016-11-19
11-18-2016
2016
un-18-2018
un-unk-2016
;run;

BUT, as I posted earlier:

how would you interpret input like: 09-05-2015
is it a ddmmyy10. informat or mmddyy10. informat ?
 
and what about: 120499 - is it 12apr1999 or 12apr2099 or 04dec1999 etc?
 
You cannot have a unique result unless you have some more restrictions.

Re: date formats

Reeza — Wed, 21 Dec 2016 09:26:30 GMT

See the notes and solution in this paper

http://support.sas.com/resources/papers/proceedings11/117-2011.pdf