Can I use Like and In function in a where statement together?

[Sandeep77]

Hi all,

I am writing a code where I need to find Hoist001, Hoist002 and so on so I have used where client_code like (Hoist%) but I also want to include some other client code. I tried to include them by using where client_code in a,b,c etc but I am getting error. So I want to know is it possible to use where statement for like and other where statement using In?

proc sql;
create table Hoist_Accs_To_Track as 
	(select	t3.icustomerid,
			t1.debt_code,
			t1.client_code, 
			t1.Rep_code,
			t2.Accountstatus,
			t2.rep_descrn as Rep_Description, 
			t1.dt_curbal as Balance, 
			t1.dt_debtval as Orig_Balance,
			datepart(t1.dt_rep_init_date) as Rep_Entry_Date format date9.,
			datepart(t1.dt_datinstr) as Bookon_Date format date9.
			from p2scflow.debt as t1
				left join p2scflow.repcode as t2 on t1.rep_code = t2.rep_code
				left join p2scflow.debtcust as t3 on t1.debt_code = t3.debt_code
					where t1.client_code like ('HOIST%')
and where t1.client_code in ('IDEM001','IDEM002','IDEM003','IDEM004','IDEM005','IDEM006','VANQUIS215','VANQUIS216','VANQUIS217','VANQUIS218','VANQUIS219'));
run;

Error log:
29         proc sql;
30         create table Hoist_Accs_To_Track as
31         	(select	t3.icustomerid,
32         			t1.debt_code,
33         			t1.client_code,
34         			t1.Rep_code,
35         			t2.Accountstatus,
36         			t2.rep_descrn as Rep_Description,
37         			t1.dt_curbal as Balance,
38         			t1.dt_debtval as Orig_Balance,
39         			datepart(t1.dt_rep_init_date) as Rep_Entry_Date format date9.,
40         			datepart(t1.dt_datinstr) as Bookon_Date format date9.
41         			from p2scflow.debt as t1
42         				left join p2scflow.repcode as t2 on t1.rep_code = t2.rep_code
43         				left join p2scflow.debtcust as t3 on t1.debt_code = t3.debt_code
44         					where t1.client_code like ('HOIST%')
45         and where t1.client_code in
                     __
                     22
                     76
45       ! ('IDEM001','IDEM002','IDEM003','IDEM004','IDEM005','IDEM006','VANQUIS215','VANQUIS216','VANQUIS217','VANQUIS218','VANQUIS
45       ! 219'));
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, GE, GET, GROUP, GT, GTT, HAVING, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

2                                                          The SAS System                              15:14 Wednesday, May 17, 2023

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
46         run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
47         
48         %LET _CLIENTTASKLABEL=;
49         %LET _CLIENTPROCESSFLOWNAME=;
50         %LET _CLIENTPROJECTPATH=;
51         %LET _CLIENTPROJECTPATHHOST=;
52         %LET _CLIENTPROJECTNAME=;
53         %LET _SASPROGRAMFILE=;
54         %LET _SASPROGRAMFILEHOST=;
55         
56         ;*';*";*/;quit;

[novinosrin]

I believe you don't need another "WHERE" after the AND boolean operator in -

/*corrected*/
where t1.client_code like ('HOIST%')
and t1.client_code in ('IDEM001','IDEM002','IDEM003','IDEM004','IDEM005','IDEM006','VANQUIS215','VANQUIS216','VANQUIS217','VANQUIS218','VANQUIS219'));

[Sandeep77]

Thank you @novinosrin  but it does not come up with any information.

"NOTE: Table WORK.HOIST_ACCS_TO_TRACK created, with 0 rows and 10 columns."

[Kurt_Bremser]

Post example data in usable form (DATA step with DATALINES, do not skip this!), so we can test your code.

[ballardw]

I'm going to go out on a limb and suggest that you may be wanting an OR instead of AND

In this code (removing  problem second WHERE) if your T1_client_code is "like" 'Hoist%' then it will never be one of the members in that list of values. The like is going to find values that start with HOIST and none of the values in the list do so you won't ever get any result.

	where t1.client_code like ('HOIST%')
and t1.client_code in ('IDEM001','IDEM002','IDEM003','IDEM004','IDEM005','IDEM006','VANQUIS215','VANQUIS216','VANQUIS217','VANQUIS218','VANQUIS219')

If you have a second variable that has the values in the list, then the above would make sense but not for a single variable.

PS: End Proc SQL with a QUIT statement, not run. otherwise the procedure continues to run.

[Sandeep77]

Thank you @ballardw I made the change and removed the second where and ended it with quit; but it does not show any result in the output data.
"NOTE: Table WORK.HOIST_ACCS_TO_TRACK created, with 0 rows and 10 columns."
I am looking for both Hoist% and the IDEM and Vanquis. It is not or but I want to includes all Hoist and the specific IDEM and Vanquis.

[Kurt_Bremser]

You can have only one WHERE clause in a SELECT.

where t1.client_code like ('HOIST%')
and t1.client_code in ('IDEM001','IDEM002','IDEM003','IDEM004','IDEM005','IDEM006','VANQUIS215','VANQUIS216','VANQUIS217','VANQUIS218','VANQUIS219')

SQL statements are executed immediately, so no RUN is needed. Terminate the SQL procedure with a QUIT statement.

[Sandeep77]

I tried doing this but it does not produce any output results. Please check the log.

29         proc sql;
30         create table Hoist_Accs_To_Track as
31         	(select	t3.icustomerid,
32         			t1.debt_code,
33         			t1.client_code,
34         			t1.Rep_code,
35         			t2.Accountstatus,
36         			t2.rep_descrn as Rep_Description,
37         			t1.dt_curbal as Balance,
38         			t1.dt_debtval as Orig_Balance,
39         			datepart(t1.dt_rep_init_date) as Rep_Entry_Date format date9.,
40         			datepart(t1.dt_datinstr) as Bookon_Date format date9.
41         			from p2scflow.debt as t1
42         				left join p2scflow.repcode as t2 on t1.rep_code = t2.rep_code
43         				left join p2scflow.debtcust as t3 on t1.debt_code = t3.debt_code
44         				where t1.client_code like ('HOIST%')
45         and t1.client_code in
45       ! ('IDEM001','IDEM002','IDEM003','IDEM004','IDEM005','IDEM006','VANQUIS215','VANQUIS216','VANQUIS217','VANQUIS218','VANQUIS
45       ! 219'));
NOTE: Table WORK.HOIST_ACCS_TO_TRACK created, with 0 rows and 10 columns.

46         					quit;

[Kurt_Bremser]

Please read my post again, with more diligence.

We cannot verify your code without data.

[Sandeep77]

Ok no problem. I don't have a sample data as this is the first code I am writing to get the data. From the code you can see that I am trying to get data from different datasets like debt, repcode and debtcust tables.

[Kurt_Bremser]

Your WHERE clause uses only columns from 

p2scflow.debt

so we must see examples for the two columns from this dataset used in the WHERE. Your code looks OK, so the reason for not getting any results is in the data.

[yabwon]

1) How about starting with:

select distinct t1.client_code
from from p2scflow.debt as t1
;

to see if the values you are looking for are in the data?

2) Your condition is always false 

where t1.client_code like ('HOIST%')
 and t1.client_code in
('IDEM001','IDEM002','IDEM003','IDEM004','IDEM005','IDEM006','VANQUIS215','VANQUIS216','VANQUIS217','VANQUIS218','VANQUIS219'));

the none of elements from your "long" list starts with "HOIST".

My suggestions:

a) If you get the list from 1) see if the values are in upper case letters. Possibly you need to do:

upcase(strip(t1.client_code))

b) There is a chance you need to replace and with or.

Bart

[Sandeep77]

Thank you. I have tried Hoist% and the IDEM, vanquis separately and they all comes in the output data when I run either Hoist or IDEM, Vanquis separately. But I wanted all the Hoist client code and Idem, Vanquis also with the Hoist and when I try to do that it does not show the output data. Now I am thinking of adding all the Hoist ones first and then create another table to add the Idem and Vanquis client code.

[yabwon · Accepted solution]

Read the points a) and  b) from my post.

where upcase(strip(t1.client_code)) like 'HOIST%'
 OR upcase(strip(t1.client_code)) in
('IDEM001','IDEM002','IDEM003','IDEM004','IDEM005','IDEM006','VANQUIS215','VANQUIS216','VANQUIS217','VANQUIS218','VANQUIS219')

[Sandeep77]

Thank you. Your solution worked 🙂