How To Use If Statement in a Join

[mhoward2]

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!

[Reeza · Accepted solution]

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!

---

[mhoward2]

Those two pieces solved it, thank you so much! Clearly I need to learn how to use functions in SAS better!