SAS proc SQL converting to matching data type in query-CodePudding

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.

CodePudding user response：

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;