BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
csetzkorn
Lapis Lazuli | Level 10

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

1 REPLY 1
Astounding
PROC Star

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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