I am trying to replace * with % in a string and than build a SQL like statement. Replacing it with tranwrd works perfect and can even use a macro variable to build the like statement.
The only problem I have is that I get a warning in the log :
WARNING: Apparent invocation of macro G not resolved.
This is obviously because of the % replacement. I tried several hiding statements but i cant get rit of the warning. Using the macro variable in the SQL statement works also perfect as you can see in the enclosed code.
OPTIONS MPRINT NOMLOGIC NOSYMBOLGEN source source2; %MACRO multi_selection(p_variabele=,p_type=); %GLOBAL &p_variabele; %GLOBAL &p_variabele._zonder; %GLOBAL &p_variabele._sql; %LET v_scheiding=; %IF &p_type=txt %THEN %LET v_scheiding=%STR(%"); %if &&&p_variabele._count=1 %THEN %DO; %LET &p_variabele._zonder=&&&p_variabele.; %LET &p_variabele=&v_scheiding.&&&p_variabele.&v_scheiding.; %LET test=&&&p_variabele._zonder; %IF %SYSFUNC(FIND(&&&p_variabele.,*)) > 0 %THEN %DO; DATA _NULL_; test1="%SYSFUNC(TRANWRD(&&&p_variabele._zonder,*,%))"; test="LIKE '" || test1 || "'"; CALL SYMPUT("&p_variabele._sql",test); RUN; %END; %END; %else %DO; %LET &p_variabele=; %do i=1 %to &&&p_variabele._COUNT; %IF &i=1 %THEN %DO; %LET &p_variabele._zonder=&&&p_variabele.&i; %LET &p_variabele=&v_scheiding.&&&p_variabele.&i.&v_scheiding.; %END; %ELSE %DO; %LET &p_variabele._zonder=&&&p_variabele._zonder.,&&&p_variabele.&i.; %LET &p_variabele=&&&p_variabele.,&v_scheiding.&&&p_variabele.&i.&v_scheiding.; %END; %END; %END; %PUT &&p_variabele._zonder: &&&p_variabele._zonder; %PUT &&p_variabele.: &&&p_variabele.; %PUT &&p_variabele._sql: &&&p_variabele._sql; %MEND; %LET test_sql=; %LET test_count=1; %LET test=*g*; %multi_selection(P_variabele=test,p_type=txt); DATA work.test; txt_field="aabbd"; output; txt_field="agod"; output; txt_field="g987b"; output; txt_field="987bg"; output; RUN; PROC SQL; CREATE TABLE work.test1 AS SELECT * FROM work.test WHERE txt_field &test_sql.; QUIT;
You don't need %SYSFUNC in Data Step.
DATA _NULL_;
*test1='%SYSFUNC(TRANWRD(&&&p_variabele._zonder,*,%))';
test1=TRANWRD("&&&p_variabele._zonder",'*','%');
test="LIKE '" ||strip(test1)|| "'";
CALL SYMPUT("&p_variabele._sql",test);
RUN;
You don't need %SYSFUNC in Data Step.
DATA _NULL_;
*test1='%SYSFUNC(TRANWRD(&&&p_variabele._zonder,*,%))';
test1=TRANWRD("&&&p_variabele._zonder",'*','%');
test="LIKE '" ||strip(test1)|| "'";
CALL SYMPUT("&p_variabele._sql",test);
RUN;
THx. that worked and you are correct you don't need sysfunc in a data step.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.