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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.