topic Re: Concat substrings for dates in SAS-Teradata in SAS Programming

Concat substrings for dates in SAS-Teradata

George_SAS — Fri, 19 Jul 2019 10:46:51 GMT

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?

Re: Concat substrings for dates in SAS-Teradata

ChrisNZ — Sun, 21 Jul 2019 11:21:39 GMT

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

Re: Concat substrings for dates in SAS-Teradata

Tom — Sun, 21 Jul 2019 18:05:33 GMT

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.

Re: Concat substrings for dates in SAS-Teradata

George_SAS — Mon, 22 Jul 2019 09:31:49 GMT

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.

Re: Concat substrings for dates in SAS-Teradata

ChrisNZ — Mon, 22 Jul 2019 09:25:37 GMT

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

Re: Concat substrings for dates in SAS-Teradata

George_SAS — Mon, 22 Jul 2019 09:30:47 GMT

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.

Re: Concat substrings for dates in SAS-Teradata

George_SAS — Mon, 22 Jul 2019 09:42:53 GMT

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.

Re: Concat substrings for dates in SAS-Teradata

ChrisNZ — Mon, 22 Jul 2019 09:50:37 GMT

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.

Re: Concat substrings for dates in SAS-Teradata

George_SAS — Mon, 22 Jul 2019 11:16:46 GMT

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

Re: Concat substrings for dates in SAS-Teradata

Tom — Mon, 22 Jul 2019 13:25:24 GMT

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.

Re: Concat substrings for dates in SAS-Teradata

George_SAS — Mon, 22 Jul 2019 14:08:55 GMT

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?

Re: Concat substrings for dates in SAS-Teradata

Tom — Mon, 22 Jul 2019 14:09:57 GMT

@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.)

Re: Concat substrings for dates in SAS-Teradata

Tom — Mon, 22 Jul 2019 14:11:37 GMT

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.

Re: Concat substrings for dates in SAS-Teradata

George_SAS — Mon, 22 Jul 2019 14:22:07 GMT

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.

Re: Concat substrings for dates in SAS-Teradata

Tom — Mon, 22 Jul 2019 14:39:54 GMT

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

Re: Concat substrings for dates in SAS-Teradata

George_SAS — Mon, 22 Jul 2019 15:01:17 GMT

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?

Re: Concat substrings for dates in SAS-Teradata

Tom — Mon, 22 Jul 2019 16:12:49 GMT

@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.

Re: Concat substrings for dates in SAS-Teradata

ChrisNZ — Mon, 22 Jul 2019 23:21:36 GMT

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.

Re: Concat substrings for dates in SAS-Teradata

George_SAS — Tue, 23 Jul 2019 10:26:08 GMT

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.