DATA Step, Macro, Functions and more

Replacing * with % in a string results in a warning in the log

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Replacing * with % in a string results in a warning in the log

[ Edited ]

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;

Accepted Solutions
Solution
‎07-12-2016 06:31 AM
Super User
Posts: 10,044

Re: Replacing * with % in a string results in a warning in the log

Posted in reply to Richardvan_tHoff

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;

View solution in original post


All Replies
Solution
‎07-12-2016 06:31 AM
Super User
Posts: 10,044

Re: Replacing * with % in a string results in a warning in the log

Posted in reply to Richardvan_tHoff

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;
Occasional Contributor
Posts: 12

Re: Replacing * with % in a string results in a warning in the log

THx. that worked and you are correct you don't need sysfunc in a data step.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 195 views
  • 0 likes
  • 2 in conversation