Hi! I want to create a macro which compare two words and which I can call inside proc sql. The problem is that my macro has another proc sql and conflict with the first. Macro for finding a count of different positions in two words: data table;
input a;
datalines;
1
;run;
%macro symbol_diff_cnt(word1=, word2=);
%global diff_cnt;
proc sql;
select max(length(&word1),length(&word2))
into: length_max
from table
;quit;
%let length_max=&length_max;
%macro test;
proc sql;
select
0 %do i=1 %to &length_max.;
+ (case when substring(&word1 from &i for 1)=substring(&word2 from &i for 1) then 0 else 1 end)
%end;
into: dif_cnt
from table
;quit;
%mend test;
%test
%let diff_cnt=&dif_cnt;
%put &diff_cnt;
%mend symbol_diff_cnt; I call this macro inside next trivial proc sql which print two words only if difference between these words < or = 1. data words;
input word $ n;
datalines;
abcdef 1
abcpef 2
;run;
proc sql;
create table wwww as
select
t1.word as s1,
t2.word as s2
from words(where=(n=1)) as t1
left join words(where=(n=2)) as t2 on %symbol_diff_cnt(word1=w1.word,word2=w2.word)<=1
;quit;
But it has errors: - because of proc sql: ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GROUP, GT, GTT, HAVING, LE, LET, LIKE, LT, LTT, NE, NET, OR, ORDER, WHERE, ^=, |, ||, ~=. ERROR 76-322: Syntax error, statement will be ignored. - because of substitution w1.word and w2.word in max(length(w1.word),length(w2.word)): ERROR: Unresolved reference to table/correlation name w1. ERROR: Unresolved reference to table/correlation name w2. - and other: ERROR: The text expression &LENGTH_MAX contains a recursive reference to the macro variable LENGTH_MAX. The macro variable will be assigned the null value. ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition. ERROR: The %TO value of the %DO I loop is invalid. ERROR: The macro TEST will stop executing. How I can correct my macro?
... View more