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 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.

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

There are at least two issues:

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

Possible solution:

  1. 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 ...
  2. 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.

View solution in original post

8 REPLIES 8
Reeza
Super User

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. 

Manu_SAS
Obsidian | Level 7

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

FreelanceReinh
Jade | Level 19

@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:

 

  1. 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=
                                                                ==
  2. 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.

Sir_Highbury
Quartz | Level 8

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

FreelanceReinh
Jade | Level 19

There are at least two issues:

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

Possible solution:

  1. 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 ...
  2. 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.

Sir_Highbury
Quartz | Level 8

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?

FreelanceReinh
Jade | Level 19

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").

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
  • 8 replies
  • 2710 views
  • 2 likes
  • 4 in conversation