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,156

Re: Macro debug call execute

Posted in reply to Sir_Highbury

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,156

Re: Macro debug call execute

Posted in reply to Sir_Highbury

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: 7,039

Re: Macro debug call execute

Posted in reply to Sir_Highbury

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: 11,343

Re: Macro debug call execute

Posted in reply to Sir_Highbury

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
  • 609 views
  • 1 like
  • 4 in conversation