Creating Alias and setting Data Type to the newly created column withouth having to relist the alias again

USING SAS EG 7.13

I have a table where I’m creating a few alias columns that are compiled from concatenated strings gathered from other fields. I’M having trouble assigning the aliased columns a data type and length without having to relist the column again which is causing warnings within SAS since the columns are being listed twice in a SELECT statement. Is there a way to create an aliased column and assign it a data type and length (like varchar(35)) without having to list the column twice?

Ive tried the following

CONCAT('SCCF ',c.CLM_ITS_SCCF_NBR) AS CLM_ITS_SCCF_NBR_FORMATTED(varchar(35))

CONCAT('SCCF ',c.CLM_ITS_SCCF_NBR) AS varchar(35) CLM_ITS_SCCF_NBR_FORMATTED

The only thing that’s worked is:

 CONCAT('SCCF ',c.CLM_ITS_SCCF_NBR) AS CLM_ITS_SCCF_NBR_FORMATTED, 
 CAST(CLM_ITS_SCCF_NBR_FORMATTED AS varchar(35)),

But again, this is giving a warning in SAS that doesn’t meet departmental standards.

Create Table XREF as SELECT * FROM CONNECTION TO TERADATA(
      SELECT
        c.COL1,
        c.COL2,
        CONCAT('SCCF ',c.COL1) AS CLM_ITS_SCCF_NBR_FORMATTED,
            CAST(CLM_ITS_SCCF_NBR_FORMATTED AS varchar(35)),
        CONCAT(COL1,',',c.COL2) AS XREF_CAK_CONCAT,
            CAST(XREF_CAK_CONCAT AS varchar(100))
    FROM EDW_ALLPHI.CLM c
INNER JOIN &ID..MY_XREF x on
x.THIS_COL = c.THAT_COL
    );

These are the warnings SAS is issuing.

WARNING: Variable CLM_ITS_SCCF_NBR_FORMATTED already exists on file WORK.XREF.

WARNING: Variable XREF_CAK_CONCAT already exists on file WORK.XREF.

Answer

You are not actually asking about ALIASes in this question. Instead you are asking about the actual variable NAMES that you are generating. Your Teradata query is selecting 6 columns (variables). Two of them you select as is so their names will stay unchanged, two you are assigning names and two you are NOT assigning any name at all.

I am surprised that it runs at all but from the error message it looks like Teradata is assigning the CAST() function calls the name of the variable that is being reformatted. Just give those two columns different names.

SELECT
    c.COL1
  , c.COL2
  , CONCAT('SCCF ',c.COL1) AS CLM_ITS_SCCF_NBR_FORMATTED
  , CAST(CLM_ITS_SCCF_NBR_FORMATTED AS varchar(35)) AS CLM_ITS_SCCF_NBR_FORMATTED_RAW
  , CONCAT(COL1,',',c.COL2) AS XREF_CAK_CONCAT
  , CAST(XREF_CAK_CONCAT AS varchar(100)) AS XREF_CAK_CONCAT_RAW

If instead you meant to only create four variables then add the CAST() function into those columns (variables) definitions.

SELECT
    c.COL1
  , c.COL2
  , CAST(CONCAT('SCCF ',c.COL1) AS varchar(35)) AS CLM_ITS_SCCF_NBR_FORMATTED
  , CAST(CONCAT(COL1,',',c.COL2) AS varchar(100)) AS XREF_CAK_CONCAT