date string to date

[csetzkorn]

I am having this type of string in my data set:

20171231

and would like to transform it into a date to ultimatley be persisted as:

DATE FORMAT 'YYYY-MM-DD'

in TeraData. I tried this in my datastep:

Test = input(FILE_DATE, yymmdd8.);
format Test DATE9.;

but get missing values (i.e. .). Any ideas?

[Astounding · Accepted solution]

Those results tend to indicate that your FILE_DATE field is actually numeric, not a string.  You can confirm that by running PROC CONTENTS on your source data.

If it is actually numeric, you would need:

test = input( put(FILE_DATE, 8.), yymmdd8.);

The PUT function converts the numeric value to a string.  Without it, SAS converts it (you probably have a note to that effect in your log) using a 12-character format and right-hand justifying the string.  (So INPUT reads 4 blanks plus 4 digits, and generates a missing value.)

Finally, date9. isn't the format you are asking for.  Instead, use:

format test yymmdd10.;

Another theoretical possibility is that Teradata is returning datetimes, not dates.  If that's the case, skip the INPUT function and use:

test = datepart(FILE_DATE);