DATA Step, Macro, Functions and more

How to find null or missing from macro variable array?

Reply
Occasional Contributor
Posts: 14

How to find null or missing from macro variable array?

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;

Trusted Advisor
Posts: 1,228

Re: How to find null or missing from macro variable array?

data have;

input max;

datalines;

1

2

.

4

6

8

;

data _null_;

set have;

if max=. then put "ERROR:Missing value found";

run;

Super User
Posts: 11,343

Re: How to find null or missing from macro variable array?

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.

Frequent Contributor
Posts: 85

Re: How to find null or missing from macro variable array?

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=','; )

Super User
Super User
Posts: 7,039

Re: How to find null or missing from macro variable array?

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;

Occasional Contributor
Posts: 14

Re: How to find null or missing from macro variable array?

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.

Respected Advisor
Posts: 4,173

Re: How to find null or missing from macro variable array?

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;

Frequent Contributor
Posts: 85

Re: How to find null or missing from macro variable array?

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;

Super User
Super User
Posts: 7,039

Re: How to find null or missing from macro variable array?

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);

Super User
Posts: 10,020

Re: How to find null or missing from macro variable array?

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

Ask a Question
Discussion stats
  • 9 replies
  • 384 views
  • 0 likes
  • 7 in conversation