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 🙂
In PROC SQL, you need the BETWEEN syntax
where DATE_OF_APPROVAL between intnx('month', today(), -1, 'B') and intnx('month', today(), -1, 'E')
In PROC SQL, you need the BETWEEN syntax
where DATE_OF_APPROVAL between intnx('month', today(), -1, 'B') and intnx('month', today(), -1, 'E')
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.
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;
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.
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.