Im looking to put an IF Statement in my join, here is the code:
PROC SQL;
CREATE TABLE WORK.WANT AS
SELECT t1.*,
t2.Group
FROM WORK.HAVE t1 INNER JOIN
WORK.GROUPS t2 ON (t1.UNIQ_ID = t2.UNIQ_ID AND t1.TYPE = t2.TYPE AND
t2.PERIOD = IF(t1.TYPE = 'ABC', SUBSTR(t1.DATE_SK, 1, 6), SUBSTR(t1.DATE_SK, 1, 4)01));
QUIT;
But I keep getting the following error:
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
Any thoughts? Thanks in advance!
There is no IF() function of that nature in SAS.
There are IFC()/IFN() functions and you can use those or use a CASE statement.
I suspect that 01 beside the substring will also cause an issue?
Did you mean to add a CATT function to concatenate the '01' value to the substring value?
PROC SQL; CREATE TABLE WORK.WANT AS SELECT t1.*, t2.Group FROM WORK.HAVE t1 INNER JOIN WORK.GROUPS t2 ON (t1.UNIQ_ID = t2.UNIQ_ID AND t1.TYPE = t2.TYPE AND t2.PERIOD = IF(t1.TYPE = 'ABC', SUBSTR(t1.DATE_SK, 1, 6), SUBSTR(t1.DATE_SK, 1, 4)01)); QUIT;
@mhoward2 wrote:
Im looking to put an IF Statement in my join, here is the code:
PROC SQL; CREATE TABLE WORK.WANT AS SELECT t1.*, t2.Group FROM WORK.HAVE t1 INNER JOIN WORK.GROUPS t2 ON (t1.UNIQ_ID = t2.UNIQ_ID AND t1.TYPE = t2.TYPE AND t2.PERIOD = IF(t1.TYPE = 'ABC', SUBSTR(t1.DATE_SK, 1, 6), SUBSTR(t1.DATE_SK, 1, 4)01)); QUIT;
But I keep getting the following error:
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.Any thoughts? Thanks in advance!
There is no IF() function of that nature in SAS.
There are IFC()/IFN() functions and you can use those or use a CASE statement.
I suspect that 01 beside the substring will also cause an issue?
Did you mean to add a CATT function to concatenate the '01' value to the substring value?
PROC SQL; CREATE TABLE WORK.WANT AS SELECT t1.*, t2.Group FROM WORK.HAVE t1 INNER JOIN WORK.GROUPS t2 ON (t1.UNIQ_ID = t2.UNIQ_ID AND t1.TYPE = t2.TYPE AND t2.PERIOD = IF(t1.TYPE = 'ABC', SUBSTR(t1.DATE_SK, 1, 6), SUBSTR(t1.DATE_SK, 1, 4)01)); QUIT;
@mhoward2 wrote:
Im looking to put an IF Statement in my join, here is the code:
PROC SQL; CREATE TABLE WORK.WANT AS SELECT t1.*, t2.Group FROM WORK.HAVE t1 INNER JOIN WORK.GROUPS t2 ON (t1.UNIQ_ID = t2.UNIQ_ID AND t1.TYPE = t2.TYPE AND t2.PERIOD = IF(t1.TYPE = 'ABC', SUBSTR(t1.DATE_SK, 1, 6), SUBSTR(t1.DATE_SK, 1, 4)01)); QUIT;
But I keep getting the following error:
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.Any thoughts? Thanks in advance!
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.