topic Re: debug macro call execute, no error, no results in SAS Programming

debug macro call execute, no error, no results

Sir_Highbury — Mon, 02 May 2016 11:30:24 GMT

Dear experts,

I already tested the following script:

/* check 03: count missing */
proc sql; create table base_missing as select VPS_AUFENTHALTSLAND,
case when VPS_AUFENTHALTSLAND in ('',' ','-','.') then 1 else 0 end as missing,
case when VPS_AUFENTHALTSLAND ='NB' then 1 else 0 end as default_value
from in.test ;run;
proc sql; create table base_missing as select sum(missing) as num_missing,
sum(default_value) as num_def_value from base_missing ;run;
proc sql; update DC.input_analysis_res
set number_missing_calc = (select num_missing FROM base_missing)
WHERE Variable_Name_Original='VPS_AUFENTHALTSLAND' ;run;
proc sql; update DC.input_analysis_res
set number_def_value = (select num_def_value FROM base_missing)
WHERE Variable_Name_Original='VPS_AUFENTHALTSLAND' ;run;

and it works correctly. Now I would like to call it in a macro:

step1:

%macro macro_missing (var=,def_val=);
proc sql; create table base_missing as select "&VAR",
case when "&VAR" in ('',' ','-','.') then 1 else 0 end as missing,
case when "&VAR" ="&def_val" then 1 else 0 end as default_value from in.test
;run;
proc sql; create table base_missing as select sum(missing) as num_missing,sum(default_value) as num_def_value
from base_missing ;run;
proc sql; update DC.input_analysis_res set number_missing_calc = (select num_missing FROM base_missing)
WHERE Variable_Name_Original="&VAR" ;run;
proc sql; update DC.input_analysis_res set number_def_value = (select num_def_value FROM base_missing)
WHERE Variable_Name_Original="&VAR" ;run;
%mend macro_missing;

step 2:

data _null_;
set DC.input_analysis_res;
if Variable='VPS_AUFENTHALTSLAND';
call execute('%macro_missing (var='||strip(variable_name)||'def_val='||strip(Default_value)||');');
run;

I get neither an error message nor the expected results. Do someone have a suggestion? I do not see any error.

Thanks in advance.

Re: debug macro call execute, no error, no results

Reeza — Mon, 02 May 2016 12:34:03 GMT

Test your macro call without call execute.

Your mistake is in how you reference the &Var macro variable. If it it is intended as a variable name do not include it in quotation marks. If it is a variable value then include it in quotation marks.

Fix that and test without call execute.

Re: debug macro call execute, no error, no results

Manu_SAS — Mon, 02 May 2016 12:41:40 GMT

Hi,

Try below statement,

call execute('%macro_missing (var='||strip(variable_name)||',def_val='||strip(Default_value)||');');

Guess you missed comma between parameters!

Regards,

Manohar

Re: debug macro call execute, no error, no results

FreelanceReinh — Mon, 02 May 2016 13:06:15 GMT

@Sir_Highbury wrote:

(...)

I get neither an error message nor the expected results. Do someone have a suggestion? I do not see any error.

Dear Sir,

A close examination of the SAS log is always helpful in situations like this. The absence of error messages is necessary, but by no means sufficient for a perfect run of a program.

When I ran your code -- after creating test data, because you didn't supply any, and replacing the inappropriate RUN statements of PROC SQL steps by QUIT statements -- I saw, for example, this line in the log:

1 +   create table base_missing as select "VPS_AUFENTHALTSLANDdef_val=NB", case when "VPS_AUFENTHALTSLANDdef_val=NB" in ('','

It shows two issues:

You missed the comma separating the macro parameters in the macro call created by CALL EXECUTE:

call execute('%macro_missing (var='||strip(variable_name)||', def_val=
                                                            ==

You try to compare a variable name (VPS_AUFENTHALTSLAND) with variable values (' ', '-' etc.). To avoid this, please remove the quotation marks around &VAR in the first three occurrences of
```
"&VAR"
```
in the macro code (but not in the last two occurrences).

These two corrections might resolve the issues, but only if you really have all of the three variables

Variable
variable_name
Variable_Name_Original

in dataset DC.input_analysis_res.

Re: debug macro call execute, no error, no results

Sir_Highbury — Wed, 25 May 2016 15:00:45 GMT

Dear experts,

I am back on the topic and thanks to your help the situation is getting better but there are still some issues. Here the first one.

1) ERROR: Expression using IN has components that are of different data types.
NOTE: The IN referred to may have been transformed from an OR to an IN at some point during
PROC SQL WHERE clause optimization.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: The following columns were not found in the contributing tables: No.
1 +
run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.07 seconds
cpu time 0.00 seconds

Basically writing:

case when &VAR. in ('',' ','-','.') then 1 else 0 end as missing,
case when &VAR. =&def_val. then 1 else 0 end as default_value

I wanted to check if the variable is missing or a default value is used, applying the same logic to charachter and numeric variables.

How can do it? The check I would like to perform involve both numbers and charachter.

Attached the whole log.

The fact that then I get the following error:

1 +
proc sql;
1 +
create
2 + table base_missing as select sum(missing) as num_missing,sum(default_value) as
num_def_value from base_missing ;
ERROR: File WORK.BASE_MISSING.DATA does not exist.
2 +
run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

seems to be a direct consecquence of the first issue.

Thanks a lot again for you help! SH

Re: debug macro call execute, no error, no results

FreelanceReinh — Wed, 25 May 2016 13:21:08 GMT

There are at least two issues:

Numeric variables &VAR cannot be compared directly to a list of character values.
A comparison of a character variable &VAR to a value &def_val requires quotes around or in &def_val.

Possible solution:

A quick (and a bit dirty) way of "applying the same logic to character and numeric variables" (as you desire) is to apply the CAT or (even better) CATS function to &VAR in order to convert its value to character:
case when cats(&VAR) in ...
Since the values of macro variable DEF_VAL do not seem to be quoted, you can put &def_val into double quotes (as you did already in your initial post) and apply the CATS function to avoid potential issues with leading blanks:
case when cats(&VAR)=cats("&def_val") then ...

And, again, PROC SQL steps should end with a QUIT statement, not a RUN statement.

Re: debug macro call execute, no error, no results

Sir_Highbury — Wed, 25 May 2016 14:59:56 GMT

Rheinard that's brilliant, thanks a lot for your your high effectiveness, flexibility and promptness.

Just one more point: basically I do ignore the difference between quit and run because it did not produce any mistake. Why should I care about it?

Re: debug macro call execute, no error, no results

FreelanceReinh — Wed, 25 May 2016 15:19:33 GMT

When you submit a single PROC SQL step without a QUIT statement, it will be executed, but the title bar of the Enhanced Editor will show the notification "PROC SQL running" to indicate that the step is not complete. Probably you didn't notice this in your program because subsequent DATA or PROC steps finish such PROC SQL steps automatically.

RUN statements in PROC SQL steps cause unnecessary messages

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

as you can see in your log. It's good practice in general to avoid unnecessary log messages, especially those with a negative statement (like "... has no effect").

Re: debug macro call execute, no error, no results

Sir_Highbury — Wed, 25 May 2016 16:18:16 GMT