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