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!
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;
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;
That did it, thank you so much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.