sas adding a percent sign to the macro values to use in proc sql like statement

Question

I basically have codes similiar to this

V2566
T67879
F7899
78902

in a sas dataset. These values,I wanted to use to store in a macro. with quotes I write this code

PROC SQL NOPRINT;
SELECT quote(CODES,"'") 
INTO :CODES  separated by ", "
FROM CODES;
QUIT;
%put macro variable CODES:&CODES;

This just added quotes. how do I modify to get codes by adding a % before and after each of these values to use like any ('%V2566%','%T67879%','%F7899%','%78902%'). That could be used in the my quote as this.

proc sql;
....
select ... where CODES like any (&CODES).

Answer 1

Just add the values in before using the QUOTE() function.

SELECT quote(cats('%',CODES,'%'),"'") 

But you will need to use pass-through SQL to use LIKE ANY.

select * from connection to mydb
(... where CODES like any (&CODES) ... )
;

If you want to similate LIKE ANY functionality then perhaps you can add more code into the macro variable?

PROC SQL NOPRINT;
SELECT 'CODES LIKE '||quote(cats('%',CODES,'%'),"'") 
  INTO :where separated by " or "
  FROM CODES
;
create table want as 
  select ....
  where &where
;
quit;