topic Re: Converting Teradata Pulled Date Data to YYYYMMDD Format in SAS Programming

Converting Teradata Pulled Date Data to YYYYMMDD Format

Sas_Act_114 — Wed, 05 Dec 2018 19:06:05 GMT

I am pulling data from a Teradata using PROC SQL on a PC-SAS (SAS 9.4 TS Level 1M3) platform. One of the issues I am having is with the date format. In the Teradata, the date is formatted as '2016-01-15' and when I pull it, SAS converts it to a 15JAN2016 format (DATE9. format). I need to convert the date to a YYYYMMDD style (20160115), which I believe would be a character variable (honestly don't know), before exporting the data to a text file.

This is the PROC SQL (I've edited the table and column names for simplicity):

%LET DATE1_ST_DT = '2018-01-01';
%LET DATE1_END_DT = '2018-01-31';
%LET DATE2_ST_DT = '2016-01-01';
%LET DATE2_END_DT = '2018-02-01';

PROC SQL;
    CONNECT TO ODBC(DATAsrc='RANDOMDB');
    CREATE TABLE EXAMPLE AS
    SELECT * FROM CONNECTION TO ODBC
	( 
SELECT
  A.DATE1,
  B.DATE2,
  C.DATE3,
  C.COSTS

FROM ASYSTEM.TABLE1 A

INNER JOIN ASYSTEM.TABLE2 B
ON A.ID_MATCH1 = B.ID_MATCH1 AND A.ID_MATCH2 = B.ID_MATCH2

INNER JOIN ASYSTEM.TABLE3 C
ON B.ID_MATCH3 = C.ID_MATCH3 AND B.ID_MATCH4 = C.ID_MATCH4
AND B.ID_MATCH5 = C.ID_MATCH5

WHERE A.DATE1 BETWEEN &DATE1_ST_DT AND &DATE1_END_DT
  AND B.DATE2 BETWEEN &DATE2_ST_DT AND &DATE2_END_DT  
);
QUIT;

Ideally I would like to be able to change the format in the same PROC SQL sequence, so that the resulting date variables are in the YYYYMMDD format, thanks!

Re: Converting Teradata Pulled Date Data to YYYYMMDD Format

r_behata — Wed, 05 Dec 2018 19:37:26 GMT

This is an untested code .

PROC SQL;
    CONNECT TO ODBC(DATAsrc='RANDOMDB');
    CREATE TABLE EXAMPLE AS
    SELECT PUT(DATE1,YYMMDDN.) AS DATE1,
		   PUT(DATE2,YYMMDDN.) AS DATE2,
		   PUT(DATE3,YYMMDDN.) AS DATE3,
		   COSTS
FROM CONNECTION TO ODBC
	( 
SELECT
  A.DATE1,
  B.DATE2,
  C.DATE3,
  C.COSTS

FROM ASYSTEM.TABLE1 A

INNER JOIN ASYSTEM.TABLE2 B
ON A.ID_MATCH1 = B.ID_MATCH1 AND A.ID_MATCH2 = B.ID_MATCH2

INNER JOIN ASYSTEM.TABLE3 C
ON B.ID_MATCH3 = C.ID_MATCH3 AND B.ID_MATCH4 = C.ID_MATCH4
AND B.ID_MATCH5 = C.ID_MATCH5

WHERE A.DATE1 BETWEEN &DATE1_ST_DT AND &DATE1_END_DT
  AND B.DATE2 BETWEEN &DATE2_ST_DT AND &DATE2_END_DT  
);
QUIT;

Re: Converting Teradata Pulled Date Data to YYYYMMDD Format

Sas_Act_114 — Wed, 05 Dec 2018 19:47:46 GMT

That did it, thank you so much!