DATA Step, Macro, Functions and more

date string to date

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 78
Accepted Solution

date string to date

[ Edited ]

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?

 


Accepted Solutions
Solution
‎06-01-2017 10:41 AM
Super User
Posts: 5,081

Re: date string to date

[ Edited ]

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

View solution in original post


All Replies
Solution
‎06-01-2017 10:41 AM
Super User
Posts: 5,081

Re: date string to date

[ Edited ]

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 112 views
  • 0 likes
  • 2 in conversation