Hi All,
I have a data set which with column MAX that has value 1,2,4,6,8 and . (missing).
I want to display a message in log saying "missing value found". So I am approaching it like this but it is not working for me. Please suggest.
%macro try/minoperator;
proc sql noprint;
select distinct max into: max_val separated by ', '
from bin_table;
quit;
%if &max_val in . %then %do;
%put "ERROR:Missing value found";
%end;
%mend try;
data have;
input max;
datalines;
1
2
.
4
6
8
;
data _null_;
set have;
if max=. then put "ERROR:Missing value found";
run;
Depending on you need and options for your log window you may get slightly different appearance using either "WARNING: " or "NOTE: " to start that message.
There are easier ways to achieve your objective but the code doesn't work because:
1. The condition should be %if . in &max_val
2. The delimiter should just be a space (otherwise if you want use a comma use %macro try/minoperator mindelimiter=','; )
Do really need the list of values stuffed into a macro variable?
It the intent is to just write a note to the log then a data step is probably best.
data _null_;
set bin_table ;
where missing(max) ;
put 'WARNING: There are missing values of MAX in BIN_TABLE.';
stop;
run;
Hi All,
Thanks for the feedback but I want to use this logic inside macro only.
Jerry- I tried to replace delimiter with space but still it doesn't return anything.
Like others already hinted your macro usage approach doesn't appear to be optimal. But then I don't know the full story so below an %if condition syntax which should return what you're after.
data bin_table;
input max;
datalines;
1
2
.
4
6
8
;
run;
%macro try/minoperator;
proc sql noprint;
select distinct max into: max_val separated by ', '
from bin_table;
quit;
%if %qsysfunc(findc("&max_val",.)) %then
%do;
%put "ERROR:Missing value found";
%end;
%mend try;
%try;
Did you fix the %IF condition too?
43 %macro try/minoperator;
44
45 proc sql noprint;
46 select distinct max into: max_val separated by ' '
47 from bin_table;
48 quit;
49
50 %if . in &max_val %then %do;
51 %put "ERROR:Missing value found";
52 %end;
53
54 %mend try;
55 %try;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
"ERROR:Missing value found"
56
57 ;
If you want variation within your preferred macro approach you can try this too:
%macro try/minoperator;
proc sql noprint;
select count(*) into: max_val
from bin_table
where max is missing ;
quit;
%if &max_val %then %do;
%put "ERROR:Missing value found";
%end;
%mend try;
I find the INDEXW() function is most useful for testing for a specific value in a delimited list.
%macro try(list);
%if %sysfunc(indexw(&list,.)) %then %put ERROR: Missing value found.;
%mend try;
%try(1 2 3 4);
%try(. 2 3);
%try(2 . 1);
%try(1.2 2.3);
Also you could count the missing value :
%macro try/minoperator;
proc sql noprint;
select count(*) into : n
from bin_table
where max is missing ;
quit;
%if &n ne 0 %then %do;
%put "ERROR:Missing value found";
%end;
%mend try;
Xia Keshan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.