Concat substrings for dates in SAS-Teradata

[George_SAS · Accepted solution]

I think I have solved the issue, since I realized that SAS Data Integration didn't totally upgrade the SAS code when registering the DB.SOURCE_TABLE within this program, which means a misunderstanding between the data flow shown within the program and the real code associated with the process. Specifically, I registered in SAS Data Integration the table DB.SOURCE_TABLE and joined it with the load process box. However, I have checked that the internal code didn't change at all and was still referring a DB.SOURCE_TABLE2 used in my first example (i.e. with a DATE format, instead of a VARCHAR format). I thought that these changes were automatically applied when you alter the schema, but it seems that only a few changes are applied and the reference to the source table in the INSERT FROM SELECT statement didn't actually change.

After this, I have applied the following code and it works completely fine:

CASE WHEN SUBSTR(FIELD_DATE, 1, 4) = 'EP00'
THEN INPUT(SUBSTR(FIELD_DATE, 5, 4) || '1127',YYMMDD8.)
ELSE INPUT(SUBSTR(FIELD_DATE, 1, 8),YYMMDD8.) END length = 8   
            format = YYMMDD8.
            informat = DATE9.
            label = 'FIELD_DATE'

Thank you so much ChrisNZ and Tom for helping me to solve this issue.

[Tom]

If the variable is a date already why are you playing games with the value?

For example this code

INPUT(PUT(FIELD_DATE,8.),BEST32.))

says take a number and convert into an 8 character string and then read that string as if it was a number.  (Note that there is no such thing as a "BEST" informat, SAS will just use the normal 32. informat instead.)

So unless the number has decimal fraction (or is too big to be displayed in 8 digits) then it will be unchanged.

And this code is not going to generate a valid date

INPUT(CATS(SUBSTR(PUT(FIELD_DATE,8.),1,4),'1127'),BEST32.)

Since you are again just using the normal 32. informat to read the generated string will be read as a regular number.  So something like 20151127 will generate 20,151,127 instead of a value like '27NOV2017'd which would be the number  21,150 since that is how many days since start of 1960.

And if FIELD_DATE did have a valid date value like '27NOV2017'd then printing it as 8 character string would yield '   21150' and taking the first 4 characters would yield '   2'.  So the result would be the number 21,127. Which is the date: '04NOV2017'd

So the code is just doing what you told it to do.

[George_SAS]

Thank you for your explanation. I was playing with the functions INPUT and PUT to check the corresponding conversion, because of the requirement of the SUBSTR function to work with a character argument in SAS, since I would like to use such a function to implement this transformation in SAS, if possible.

[Tom]

---

@George_SAS wrote:

Thank you for your explanation. I was playing with the functions INPUT and PUT to check the corresponding conversion, because of the requirement of the SUBSTR function to work with a character argument in SAS, since I would like to use such a function to implement this transformation in SAS, if possible.

---

SUBSTR() works the same in most languages.  It takes part of a character string.

If you want to convert a DATE value to a string then use PUT() function with date format.

put(field_date,yymmddn8.)

If you want to convert a string into a DATE value then use INPUT() function with a date informat.

input('20191127',yymmdd8.)