topic Re: Left Join with where clause in New SAS User

Left Join with where clause

Kirito1 — Thu, 09 Feb 2023 12:40:22 GMT

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 🙂

Re: Left Join with where clause

PaigeMiller — Thu, 09 Feb 2023 13:09:13 GMT

In PROC SQL, you need the BETWEEN syntax

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

Re: Left Join with where clause

Kirito1 — Fri, 10 Feb 2023 04:55:43 GMT

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.

Re: Left Join with where clause

Kirito1 — Fri, 10 Feb 2023 07:00:38 GMT

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;

Re: Left Join with where clause

PaigeMiller — Fri, 10 Feb 2023 11:31:07 GMT

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.