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.
There are at least two issues:
Possible solution:
And, again, PROC SQL steps should end with a QUIT statement, not a RUN statement.
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.
Hi,
Try below statement,
call execute('%macro_missing (var='||strip(variable_name)||',def_val='||strip(De
Guess you missed comma between parameters!
Regards,
Manohar
@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:
call execute('%macro_missing (var='||strip(variable_name)||', def_val= ==
"&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
in dataset DC.input_analysis_res.
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
There are at least two issues:
Possible solution:
And, again, PROC SQL steps should end with a QUIT statement, not a RUN statement.
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?
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").
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.