SAS proc SQL converting to matching data type in query

Question

This is the first time I have had an issue with mis-matched data types on an INNER JOIN. One thing to note, I am not able to change the data types of the source columns.

Here is an example of the proc SQL I am trying to run:

CREATE TABLE SC_STEP_1 AS
SELECT (INPUT(A.ACCOUNT_NBR, $32.) AS A.ACCOUNT_NBR_INT),
A.OPEN_DT,
A.STATUS_CD,
A.STATUS_DT
FROM ACCOUNT A
INNER JOIN ACCOUNT_DETAIL B
ON A.ACCOUNT_NBR_INT=B.ACCOUNT_ID
WHERE A.STATUS_DT>=2022-03-14 AND A.STATUS_CD="Z";
quit;

The first attempt without trying to match the data types resulted in data mis-match errors. A.ACCOUNT_NBR is a CHAR(32) but the column only contains numbers. B.ACCOUNT_ID is an INTEGER that only contains numbers.

This was the error block of running the above to try and match data types

50         SELECT (INPUT(A.ACCOUNT_NBR, $32.) AS A.ACCOUNT_NBR_INT),
                                              __
                                              22
                                              76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

I have gotten so many other errors just drawing at straws with the syntax from "Expecting Format" to "Expecting a quoted string" to even just "Expecting ), -" and I have no idea how to continue or how to make this work. Based on the research and googling it today, I have found many examples that seem like they should work but I cannot get any syntax to pass and documentation I have found has been light at best.

Hopefully someone can point me in the right direction or if I have to attack this from a completely different way.

Update: Based on comments, I have attempted the following instead by loading the data into a temp table:

proc sql;
CREATE TABLE SC_STEP_11 (ACCOUNT_NBR_INT integer, OPEN_DT date, STATUS_CD CHAR(1), STATUS_DT date);
quit;

proc sql;
SELECT A.ACCOUNT_NBR,
A.OPEN_DT,
A.STATUS_CD,
A.STATUS_DT,
INTO WORK.SC_STEP_11
FROM ACCOUNT A
WHERE A.STATUS_DT>=2022-03-14 AND A.STATUS_CD="Z";
quit;

The table generates fine but I am getting a "Expecting a :." error:

61         INTO WORK.SC_STEP_11
                ____
                79
                76
ERROR 79-322: Expecting a :.

ERROR 76-322: Syntax error, statement will be ignored.

Answer 1

• INTO in this context is invalid in SAS, it's used to create macro variables not tables. Use CREATE TABLE instead.
• Date literals/constants need to be specified as DATE9 format, enclosed in quotes with a d after, e.g. '14Mar2022'd

Your SAS code should be:

proc sql;
create table sc_step_11 as
SELECT A.ACCOUNT_NBR,
A.OPEN_DT,
A.STATUS_CD,
A.STATUS_DT,
FROM ACCOUNT A
WHERE A.STATUS_DT>= '14Mar2022'd AND A.STATUS_CD="Z";
quit;

What happens if you run something like the following, fixing the other query issues.

PROC SQL;
CREATE TABLE SC_STEP_1 AS
SELECT INPUT(A.ACCOUNT_NBR, $32.) AS ACCOUNT_NBR_INT,
A.OPEN_DT,
A.STATUS_CD,
A.STATUS_DT
FROM ACCOUNT A
INNER JOIN ACCOUNT_DETAIL B
ON INPUT(A.ACCOUNT_NBR, $32.) = B.ACCOUNT_ID
WHERE A.STATUS_DT>='14Mar2022'd AND A.STATUS_CD="Z";
quit;