topic How to call a macro with proc sql inside another pros sql? in New SAS User

How to call a macro with proc sql inside another pros sql?

elessar — Tue, 11 Dec 2018 09:53:09 GMT

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?

Re: How to call a macro with proc sql inside another pros sql?

LinusH — Tue, 11 Dec 2018 13:34:21 GMT

Simply described, macro is a code generator.

It means that the generated code must fit into the environment that calls it.

Since SQL doesn't allow calls to a separate PROC SQL session, this can't be done using a macro either.

I'm not sure what you try to accomplish (please describe using plain language), but bottom line is tyou can't execute code in the macro that query data, you need a way to resolve this without using PROC or data step (function calls using %sysfunc is generally allowed),

Re: How to call a macro with proc sql inside another pros sql?

Tom — Tue, 11 Dec 2018 13:50:41 GMT

You don't. You cannot nest data steps/procedure steps inside of each other. When SAS sees the start of a new step it finishes compiling the current step and runs it.

Also do not place the macro definitions inside of each other. It does nothing other than confuse the programmer.

Your program does not need to start and stop PROC SQL so many times. Just have your subroutine macro generate the SELECT statement only. Then only call it when already running PROC SQL.

Perhaps something like this:

%macro test;
%local i ;
   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 :diff_cnt trimmed
   from table
  ;
%mend test;
%macro symbol_diff_cnt(word1=, word2=);
%global diff_cnt;

proc sql noprint ;
  select max(length(&word1),length(&word2))
    into :length_max trimmed
    from table
  ;

 %test

 %put &diff_cnt;
quit;

%mend symbol_diff_cnt;

However the logic of your SQL also looks strange.

Why are you using that strange SUBSTRING() function syntax instead of using the normal SUBSTR() or SUBSTRN() function? Are you pushing this code into a remote database?

Why are you calculating the maximum length of the new variables for just the first observations from TABLE? Did you mean to call the SQL aggregate function MAX() in addition to the SAS statistical function MAX(,)?

One thing to always remember with macro code is that it is mainly used to generate code. So before trying to create a macro it is important to first get the SAS code that you want to generate figured out. Then you can evaluate if you can generate that code using a macro or not.

Re: How to call a macro with proc sql inside another pros sql?

Tom — Tue, 11 Dec 2018 13:54:03 GMT

Please explain what you are trying to do. This really doesn't look like a problem that needs either macro code or SQL code. It probably could be done much easier in a data step.

Re: How to call a macro with proc sql inside another pros sql?

elessar — Wed, 12 Dec 2018 11:23:44 GMT

Hi! I rewrote code for macro without proc sql:

%macro symbol_diff_cnt(word1=, word2=);

	%let buffer = 0;

	%do i=1 %to %sysfunc(max(%length(&word1),%length(&word2)));	
		%if (%qsubstr(&word1, &i, 1) ^= %qsubstr(&word2, &i, 1)) %then %do;
			%let buffer = %eval(&buffer + 1);
		%end;
	%end; 

	&buffer;

%mend symbol_diff_cnt;

and it works if I call it by strings like that (in this example difference between meanings of word1 and word2 equals 2):

data aaa;
   input a;
   datalines;
1
;run;

%let new = %symbol_diff_cnt(word1='abcdef',word2='adcpef');
%put new = &new;

proc sql;
	create table SSS as
	select 
		'abcdef'	as s1,
		'adcpef'	as s2,
		&new.	as dif
	from aaa
;quit;

But if I substitute variables, macro compare its names, but not its meanings (in this example difference between names "t1.word" and "t2.word" equals 1):

data words;
   input word $ n;
   datalines;
abcdef 1
adcpef 2
;run;

%let neww = %symbol_diff_cnt(word1=t1.word,word2=t2.word);
%put neww = &neww;

proc sql;
	create table TTT as
	select 
		t1.word as slovo1,
		t2.word as slovo2,
		&neww.				as diff
	from words(where=(n=1))				t1
		left join words(where=(n=2))	t2	on t2.n=t1.n+1
;quit;

How can I force macro to compare meanings of variables, not its names?