check a value in the macro variable list

[ZRick]

%macro t;

proc contents data=sashelp.class out=class_vards;run;

proc sql;

select name into: varlst seperated by ',' from class_vards;

quit;

%if "Rating" in (&varlst) %then %put "it is in the list";

%else %return;

%mend;

%t

It says:

ERROR: Required operator not found in expression: "Rating" in (&varlst)

How do I fix it?

[MichelleHomes]

To use the in operator in the macro facility you need to be using SAS 9.2 or SAS 9.3 and have 2 macro options set, minoperator and mindelimiter.

This is documented in the usage note, http://support.sas.com/kb/35/591.html - Click on the Full Code tab.

cheers,

Michelle

[ZRick]

%macro t;

proc contents data=sashelp.class out=class_vards;run;

proc sql;

select name into: varlst seperated by ',' from class_vards;

quit;

%if "Rating" NOT in (&varlst) %then %put "it is NOT in the list";

%else %put "Yes, it is in the list, continue to process";

%mend;

%t

[MichelleHomes]

An alternative could be to use the %index macro function as in the SAS documentation, http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000543562.htm

But this method would require you to write a macro do loop to pass each value to test it.

[Ksharp]

or try this one :

%macro t;
%local var;
proc sql noprint;
select name into: var from dictionary.columns where libname='SASHELP' and memname='CLASS' and upcase(name)='SEX';
quit;
%if &var ne %then %put "it is in the list";.
%else %return;
%mend;

%t

Ksharp

Message was edited by: xia keshan Opps, missing something

[Tom]

If you cannot get MINOPERATOR options to work for you (or just because it is easier ) you can use %SYSFUNC() to use the INDEXW() function.

%macro t;

%local class_vards;

proc contents noprint data=sashelp.class out=class_vards;run;

proc sql noprint;

select name into: varlst separated by ',' from class_vards;

quit;

%if %sysfunc(indexw(%qupcase(&varlst),AGE,%str(,))) %then %put "it is in the list";

%else %return;

%mend;

%t;

Other points:

Watch out for case when searching, you might want to add select UPCASE(NAME) in the SQL code.

Note that you do NOT want the double quotes as there will not be quotes in the value of the macro variable that PROC SQL generated.

Use the NOPRINT option on PROC CONTENTS and PROC SQL to avoid cluttering your SAS output.

Using comma as the delimiter makes the string harder to use in macro logic (that is why I used Qupcase function) and normal SAS code.  Why not use a space as the delimiter instead?

I would NOT use the %RETURN macro command as it violates the principle of single entry and single exit for program blocks.