DATA Step, Macro, Functions and more

Macro debug call execute

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

Macro debug call execute

[ Edited ]

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;

 

 


Accepted Solutions
Solution
‎05-02-2016 03:57 AM
Respected Advisor
Posts: 3,124

Re: Macro debug call execute

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.

View solution in original post


All Replies
Solution
‎05-02-2016 03:57 AM
Respected Advisor
Posts: 3,124

Re: Macro debug call execute

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.

Frequent Contributor
Posts: 127

Re: Macro debug call execute

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

Super User
Super User
Posts: 6,500

Re: Macro debug call execute

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.

 

Super User
Posts: 10,500

Re: Macro debug call execute

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.

 

 

 

Frequent Contributor
Posts: 127

Re: Macro debug call execute

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 598 views
  • 1 like
  • 4 in conversation