Left Join with where clause

[Kirito1]

I was working on SAS where I have to apply where clause with left join. Kindly, help.

I want to filer this left join with the function below:

where intnx('month', today(), -1, 'B') <= DATE_OF_APPROVAL <= intnx('month', today(), -1, 'E');

I want to use above code  in the Code as below: 

PROC SQL;
CREATE TABLE EMPC_VPA AS
SELECT  T1.VPAA, T1.ST, T1.DATE_OF_APPROVAL, T1.DATE_OF_ONBOARD, T1.FIN_EMP_CODE,T1.Emp_Code, T1.target, T2.*
FROM DST_EMP1 T1 
left join DEC_TXN1 T2 on T1.VPAA =(T2.VPA);
QUIT;

Thanks in Advance to all the contributors. Any help is appreciated 🙂

[PaigeMiller · Accepted solution]

In PROC SQL, you need the BETWEEN syntax

where DATE_OF_APPROVAL between intnx('month', today(), -1, 'B') and intnx('month', today(), -1, 'E')

[Kirito1]

I am not sure how to write that in SAS code, I tried putting the where clause on the data(DST_EMP1) but it is giving me an error with the following code as below:

PROC SQL;
CREATE TABLE EMPC_VPA AS
SELECT  T1.VPAA, T1.ST, T1.DATE_OF_APPROVAL, T1.DATE_OF_ONBOARD, T1.FIN_EMP_CODE,T1.Emp_Code, T1.target, T2.*
FROM DST_EMP1 T1 where DATE_OF_APPROVAL between intnx('month', today(), -1, 'B') and intnx('month', today(), -1, 'E') 
left join DEC_TXN1 T2 on T1.VPAA =(T2.VPA);
QUIT;

Error as below:

PROC SQL;
77    CREATE TABLE EMPC_VPA AS
78    SELECT  T1.VPAA, T1.ST, T1.DATE_OF_APPROVAL, T1.DATE_OF_ONBOARD, T1.FIN_EMP_CODE,T1.Emp_Code, T1.target, T2.*
79    FROM DST_EMP1 T1 where DATE_OF_APPROVAL between intnx('month', today(), -1, 'B') and intnx('month', today(), -1, 'E')
80    left join DEC_TXN1 T2 on T1.VPAA =(T2.VPA);
      ----
      22
      76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, AND, EXCEPT, GROUP, HAVING, INTERSECT, OR, 
              ORDER, OUTER, UNION, |, ||.  
ERROR 76-322: Syntax error, statement will be ignored.

Kindly, help in correcting the syntax.

[Kirito1]

After some tries I got the result. Thank You @PaigeMiller 

The correct syntax is as below code:

PROC SQL;
CREATE TABLE EMPC_VPA AS
SELECT  T1.VPAA, T1.ST, T1.DATE_OF_APPROVAL, T1.DATE_OF_ONBOARD, T1.FIN_EMP_CODE,T1.Emp_Code, T1.target, T2.*
FROM DST_EMP1(where = (DATE_OF_APPROVAL between intnx('month', today(), -1, 'B') and intnx('month', today(), -1, 'E'))) T1
left join DEC_TXN1 T2 on T1.VPAA =(T2.VPA);
QUIT;

[PaigeMiller]

or alternatively using the WHERE clause in PROC SQL

PROC SQL;
    CREATE TABLE EMPC_VPA AS
        SELECT  T1.VPAA, T1.ST, T1.DATE_OF_APPROVAL,
        T1.DATE_OF_ONBOARD, T1.FIN_EMP_CODE,T1.Emp_Code, T1.target,  
        T2.*
    FROM DST_EMP1 T1
        left join DEC_TXN1 T2
        on T1.VPAA = T2.VPA
    where t1.DATE_OF_APPROVAL between intnx('month', today(), -1, 'B') and intnx('month', today(), -1, 'E');
QUIT;

Please also note the indentation and limiting the amount of text on a given line. This makes the code easier to read and understand. We request you present code in this fashion in the future to help us, and by the way, it will also help you in the long run to make your own code easier to read and understand.