Macro debug call execute

[Sir_Highbury]

Dear experts,

I am trying to debug a macro but I do not understand the error, attached the log. Does anyone of you understand it?

I limited the macro to just one case (if Variable_value_list ne "" then */ --- replaced by ---> if variable_name='VPS_AUFENTHALTSLAND' then) and the case standalone (running it outside the macro) wrok.

options mlogic SYMBOLGEN;
%macro In_list (var=,vals=); /* check */
proc sql; 
create table values_found as
select distinct &VAR 
from in.test 
group by 1; 
run;


proc sql; 
create table &vals as 
select distinct variable_&vals 
from DC.input_analysis_res 
where variable_name='&VAR' ;
run;


proc sql; 
create table out_of_&vals as 
select distinct a.*, b.* 
from values_found a, &vals b ;
run;

proc sql; 
create table out_of_&vals as 
select &VAR from out_of_&vals 
where Variable_&vals not contains &VAR ;
run;


proc sql; 
create table out_of_&vals as 
select b.&VAR, count(*) as _n_ 
from out_of_&vals a, in.test b
where a.&VAR=b.&VAR group by 1 ;
run;


proc sql; 
create table number_unexpected_values as 
select sum(_n_) as number_unexpected_values 
from out_of_&vals;
run;

data unexpected_values_list (drop=&VAR rename=(tmp=&VAR));
length tmp $2000; 
set out_of_&vals end=last; 
retain tmp; 
tmp=catx(",",tmp,&VAR); 
if last then output ;
run;

proc sql; 
update DC.input_analysis_res
set number_unexpected_value = (select number_unexpected_values FROM number_unexpected_values) 
WHERE Variable_Name_Original='&VAR' ;
run;

proc sql; 
update DC.input_analysis_res
set Unexpected_values = (select &VAR FROM unexpected_values_list) 
WHERE Variable_Name_Original='&VAR' ;
run;

%mend In_List;
 
 
data _null_;
set DC.input_analysis_res;
/* check 01
if Variable_value_list ne "" then */
if variable_name='VPS_AUFENTHALTSLAND' then
call execute('%In_List (var='||strip(variable_name)||',vals='||strip(variable_value_list)||');');
run;

[Haikuo · Accepted solution]

Quick glance at your code, some usual suspects caught my eyes, '&VAR' , is this your real intention? macro instructions will not resolve inside  a pair of single quote. use double quote instead: "&VAR" to make it resolve. Not sure if this is your only issues.

[Sir_Highbury]

Thanks Haikuo,

there are still some errors in the log but, after correcting the code as you suggested, I get the results I expect.

Thanks a lot man!

BRs, Highbury

[Tom]

The line with the error on it show pretty clearly to me the logic error in your approach.

 create table DE FR JP as select distinct variable_DE FR JP from DC.input_analysis_res

You seem to be passing in a space delimited list of values (DE FR JP) and are trying to treat it as both a dataset name and a variable name.  Neither of those can have spaces in them.

[ballardw]

These lines are all invalid when &VALS has more than one value

proc sql; create table &vals as select distinct variable_&vals from DC.input_analysis_res where variable_name='&VAR' ;run;
proc sql; create table out_of_&vals as select distinct a.*, b.* from values_found a, &vals b ;run;
proc sql; create table out_of_&vals as select &VAR from out_of_&vals where Variable_&vals not contains &VAR ;run;
proc sql; create table out_of_&vals as select b.&VAR, count(*) as _n_ from out_of_&vals a, in.test b

Create table can only create one table so if

create table &vals

Generates create table AB CD EF

or expects data from multiple sets such as in: from out_of_&vals a

That is what is causing errors like this:

NOTE: Line generated by the CALL EXECUTE routine.
1   +
create table DE FR JP as select distinct variable_DE FR JP from DC.input_analysis_res

--                                                                                             
--

One suspects a spelling error somewhere with this line;

ERROR: File WORK.NUM_OURLIERS.DATA does not exist.

Suspecting that NUM_OUTLIERS may be intended.

Also messages like this one:

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.

May indicate that a LAST Proc sql is still running if the proc hasn't encountered another run boundary in the code. Remember that SQL wants a QUIT to end.

[Sir_Highbury]

Dear Ballardw and Tom,

sorry but I do not get your remark. If what you say is correct, why using the code (here below) outside the macro, the results are exactly as expected?

proc sql; create table values_found as select distinct VPS_AUFENTHALTSLAND from in.test group by 1; run;
proc sql; create table value_list as select distinct variable_value_list from DC.input_analysis_res where variable_name='VPS_AUFENTHALTSLAND' ;run;
proc sql; create table out_of_value_list as select distinct a.*, b.* from values_found a, value_list b ;run;
proc sql; create table out_of_value_list as select VPS_AUFENTHALTSLAND from out_of_value_list where Variable_value_list not contains VPS_AUFENTHALTSLAND ;run;
proc sql; create table out_of_value_list as select b.VPS_AUFENTHALTSLAND, count(*) as _n_ from out_of_value_list a, in.test b
where a.VPS_AUFENTHALTSLAND=b.VPS_AUFENTHALTSLAND group by 1 ;run;
proc sql; create table number_unexpected_values as select sum(_n_) as number_unexpected_values from out_of_value_list;run;
data unexpected_values_list (drop=VPS_AUFENTHALTSLAND rename=(tmp=VPS_AUFENTHALTSLAND));
length tmp $2000; set out_of_value_list end=last; retain tmp; tmp=catx(",",tmp,VPS_AUFENTHALTSLAND); if last then output ;run;
proc sql; update DC.input_analysis_res
set number_unexpected_value = (select number_unexpected_values FROM number_unexpected_values) WHERE Variable_Name_Original='VPS_AUFENTHALTSLAND' ;run;
proc sql; update DC.input_analysis_res
set Unexpected_values = (select VPS_AUFENTHALTSLAND FROM unexpected_values_list) WHERE Variable_Name_Original='VPS_AUFENTHALTSLAND' ;run;

I have the problem only when I try to call the code within the macro. Thanks again for your explanation.