BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sir_Highbury
Quartz | Level 8

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

5 REPLIES 5
Haikuo
Onyx | Level 15

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
Quartz | Level 8

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
Super User Tom
Super User

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
Super User

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
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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