Concat substrings for dates in SAS-Teradata

[George_SAS]

Hi everyone,

I have performed a job to load data from a source table to a target table in Teradata with SAS Data Integration. However, I am facing an issue with a specific transformation related to a date format. For example, I need to transform the specific date 01/04/2015 to 27/11/2015, in a way that the target table in Teradata must contain the date 27/11/2015 instead of the date 01/04/2015 after the load job.

For this task, I am applying the following code, but I am getting the value 04/11/2017 instead of 27/11/2015 (the rest of dates provided by the 'ELSE' clause are correct):

proc sql;
      insert into DB.TARGET_TABLE (   
         DBCREATE_TABLE_OPTS = 'primary index NONAME1 ("BUDGET") ', 
         BUDGET, FIELD_DATE
      )
      select
         SOURCE_TABLE.BUDGET length = 12   
            format = $12.
            informat = $12.
            label = 'BUDGET',
         CASE WHEN (INPUT(PUT(FIELD_DATE,8.),BEST32.)) = '01APR2015'd THEN INPUT(CATS(SUBSTR(PUT(FIELD_DATE,8.),1,4),'1127'),BEST32.) ELSE INPUT(SUBSTR(PUT(FIELD_DATE,8.),1,8),BEST32.) END FORMAT DATE9. AS FIELD_DATE length = 8   
            format = DATE9.
            informat = DATE9.
            label = 'FIELD_DATE',
      from
         DB.SOURCE_TABLE as SOURCE_TABLE

The format of the attribute FIELD_DATE of the mentioned tables in Teradata is 'YYYYMMDD'.

Any idea?

[ChrisNZ]

Why doesn't his work?

      
CASE WHEN FIELD_DATE = '01APR2015'd 

THEN mdy(11, 27, year(FIELD_DATE)) 

ELSE FIELD_DATE END 

FORMAT DATE9. AS FIELD_DATE length = 8   
        

[George_SAS]

Thank you for your response, I have tried with your code and my example works completely fine. The problem here is that in a realistic context I have to deal with a source table containing a FIELD_DATE with VARCHAR format (instead of a DATE format) and with values like 'EP002019', which refers to extra pays of a particular year (in this case, 2019), so I need to load into the target table something like '27/11/2019', that's why I used the SUBSTR function in my previous example.

In this case, I am applying the functions SUBSTR and MDY but it does not change such an expression 'EP002019':

CASE WHEN INPUT(SUBSTR(PUT(FIELD_DATE,8.),1,4),DATE9.) = 'EP00' THEN MDY(11, 27, YEAR(FIELD_DATE)) ELSE INPUT(SUBSTR(PUT(FIELD_DATE,8.),1,8),BEST32.) END FORMAT DATE9. AS FIELD_DATE length = 8   
            format = DATE9.
            informat = DATE9.
            label = 'FIELD_DATE',

I can easily perform this transformation in Teradata by the SUBSTR function and standard SQL with the following statement:

CASE WHEN SUBSTR(FIELD_DATE, 1, 4) = 'EP00' THEN SUBSTR(FIELD_DATE, 5, 4) || '1127' ELSE SUBSTR(FIELD_DATE, 1, 8) END

but I don't know how to implement it in SAS.

[ChrisNZ]

The teradata code you posted at the end of your reply is valid SAS code.

[George_SAS]

With the Teradata code:

CASE WHEN SUBSTR(FIELD_DATE, 1, 4) = 'EP00' THEN SUBSTR(FIELD_DATE, 5, 4) || '1127' ELSE SUBSTR(FIELD_DATE, 1, 8) END

in SAS I get the error Function SUBSTR requires a character expression as argument 1.

In this case, the format of FIELD_DATE in the source table is VARCHAR(8), whereas the corresponding field of the target table is associated with a DATE format.

[ChrisNZ]

This makes no sense. If you test for value EP00 in this variable then the variable can only be a string
CASE WHEN SUBSTR(FIELD_DATE, 1, 4) = 'EP00' THEN SUBSTR(FIELD_DATE, 5, 4) || '1127' ELSE SUBSTR(FIELD_DATE, 1, 😎 END

Something is very unclear.

[George_SAS]

Let me simplify the problem: if I have the value '04012019' in the source table with VARCHAR format and I want to load the value '27/11/2019' in the target table with DATE format, then the previous code in SAS referring to these values still yields the error message "Function SUBSTR requires a character expression as argument 1":

CASE WHEN SUBSTR(FIELD_DATE, 1, 4) = '0401'
THEN SUBSTR(FIELD_DATE, 5, 4) || '1127'
ELSE SUBSTR(FIELD_DATE, 1, 8) END

but I can run the process without this error message by applying the following code:

CASE WHEN SUBSTR(PUT(FIELD_DATE,8.), 5, 4) = '0401'
THEN INPUT(SUBSTR(PUT(FIELD_DATE,8.), 1, 4) || '1127',BEST32.)
ELSE INPUT(SUBSTR(PUT(FIELD_DATE,8.), 1, 8),BEST32.) END length = 8

however, the value '27/11/2019' is not loaded into the target table and the value '01/04/2019' is actually loaded into the target table (i.e. no transformations are applied).

[Tom]

If you want to create a DATE value then you need to read your strings using a INFORMAT that generates a DATE value.

CASE WHEN SUBSTR(PUT(FIELD_DATE,8.), 5, 4) = '0401'
THEN INPUT(SUBSTR(PUT(FIELD_DATE,8.), 1, 4) || '1127',yymmdd8.)
ELSE INPUT(SUBSTR(PUT(FIELD_DATE,8.), 1, 8),yymmdd8.) 
END as NEW_DATE_VARIABLE format=yymmdd10.

At this point you have made three completely different claims about what type of data is in the variable FIELD_DATE.

Your original post you claimed to have a DATE value (a number representing the number of days since 1960). Then you said it was a string with values like 27/11/2017 or perhaps like 20171127, which was not clear.

And now in this code you are telling us that instead FIELD_DATE is a numeric field with integers that LOOK like dates in YY,YYM,MDD format. 

[George_SAS]

I am applying this code but it doesn't work, I don't get error messages but I obtain NULL values after the load process:

CASE WHEN SUBSTR(PUT(FIELD_DATE,8.),5,4) = '0401'
THEN INPUT(SUBSTR(PUT(FIELD_DATE,8.),1,4) || '1127',yymmdd8.)
ELSE INPUT(SUBSTR(PUT(FIELD_DATE,8.),1,8),yymmdd8.)
END AS NEW_DATE_VARIABLE format=yymmdd10. length = 8   
            format = DATE9.
            informat = DATE9.
            label = 'FIELD_DATE'

On the other hand, in my first example, the format in Teradata of FIELD_DATE was DATE (i.e. the source and target tables are defined with that type of format), as mentioned above. Then, I have checked that the function mdy stressed by ChrisNZ works completely fine, as mentioned above. However, a major problem arises if the format of FIELD_TABLE in the source table is VARCHAR and I just questioned about this case.

Any idea?

[Tom]

If it is a character string why are you treating it as if it was a number?  No need to use PUT() function to convert it to a string if it is already a string.

[George_SAS]

I previously applied the following code without the PUT function, but I got the error message "Function SUBSTR requires a character expression as argument 1":

CASE WHEN SUBSTR(FIELD_DATE, 1, 4) = '0401'
THEN SUBSTR(FIELD_DATE, 5, 4) || '1127'
ELSE SUBSTR(FIELD_DATE, 1, 8) END

These are the specific definitions of the source and target tables in Teradata:

CREATE MULTISET TABLE DB.SOURCE_TABLE ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      BUDGET VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
      FIELD_DATE VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( BUDGET );

CREATE MULTISET TABLE DB.TARGET_TABLE ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      BUDGET VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
      FIELD_DATE DATE FORMAT 'YYYYMMDD')
PRIMARY INDEX ( BUDGET );

Thanks again for any help you can provide.

[Tom]

If this is all in Teradata why are you even writing any SAS code?

My teradata is rusty but something like this:

execute (
insert into target_table (source,field_date)
select source,cast(field_date) as date(0) format 'yyyymmdd'
from source_table
) by teradata;

If you have pulled the data into SAS then variable FIELD_DATE from SOURCE_TABLE is a character string.

select source
     , case when (substr(field_date,1,4)='0401') then input(substr(field_date,1,4)||'1127',yymmdd8.)
            else input(field_date,yymmdd8.)
       end as field_date format=yymmdd10.
from source_table

[George_SAS]

I want to carry out this process with SAS Data Integration, I mentioned this detail in my first message.

Do you mean that the process exceeds the capabilities of SAS? I just need to convert a value 'EP00YYYY' of a source table to the day 27 November YYYY of a target table, with the definitions above, just this.

Any idea?

[Tom]

---

@George_SAS wrote:

I want to carry out this process with SAS Data Integration, I mentioned this detail in my first message.

Do you mean that the process exceeds the capabilities of SAS? I just need to convert a value 'EP00YYYY' of a source table to the day 27 November YYYY of a target table, with the definitions above, just this.

Any idea?

---

So if that is you actual problem?

Then you should be able to tell DI that this is the rule for converting FIELD_DATE from source table into FIELD_DATE in target table.

For example you could build a DATE9 style string from the last 4 characters in the EP00.... values and your constant day and month and then use the appropriate informat for the other values.  So if you strings look like 'DDMMYYYY' strings then use the DDMMYY informat.

case when (field_date like 'EP00____') then input('27NOV'||substr(field_date,5,4),date9.)
  else input(field_date,ddmmyy8.) end format=yymmdd10.

[ChrisNZ]

I just need to convert a value 'EP00YYYY' of a source table to the day 27 November YYYY of a target table, with the definitions above, just this.

Before getting stuck in the code, we need to solve this issue: How on earth does a Teradata VARCHAR variable arrive in SAS as a numeric variable (that gives you the "Function SUBSTR requires a character expression as argument 1" message) ? 

Until this is answered and solved, there is no point continuing as you'l never see the value EP00 that you are seeking in a numeric variable.