topic Re: How to find null or missing from macro variable array? in SAS Programming

How to find null or missing from macro variable array?

Anna_nag — Fri, 05 Sep 2014 20:53:47 GMT

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;

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

stat_sas — Fri, 05 Sep 2014 21:32:19 GMT

data have;

input max;

datalines;

;

data _null_;

set have;

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

run;

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

ballardw — Fri, 05 Sep 2014 22:44:17 GMT

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.

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

JerryLeBreton — Fri, 05 Sep 2014 23:49:00 GMT

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

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

Tom — Sat, 06 Sep 2014 00:22:56 GMT

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;

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

Anna_nag — Sat, 06 Sep 2014 01:19:28 GMT

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.

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

Patrick — Sat, 06 Sep 2014 02:50:25 GMT

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;

;

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;

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

JerryLeBreton — Sat, 06 Sep 2014 03:01:39 GMT

Did you fix the %IF condition too?

43 %macro try/minoperator;

45 proc sql noprint;

46 select distinct max into: max_val separated by ' '

47 from bin_table;

48 quit;

50 %if . in &max_val %then %do;

51 %put "ERROR:Missing value found";

52 %end;

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"

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;

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

Tom — Sat, 06 Sep 2014 12:16:31 GMT

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

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

Ksharp — Sat, 06 Sep 2014 13:30:53 GMT

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