topic Re: check a value in the macro variable list in SAS Programming

check a value in the macro variable list

ZRick — Sat, 29 Jun 2013 20:25:06 GMT

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

It says:

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

How do I fix it?

Re: check a value in the macro variable list

MichelleHomes — Sat, 29 Jun 2013 21:31:59 GMT

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

Re: check a value in the macro variable list

ZRick — Sat, 29 Jun 2013 22:55:04 GMT

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

Re: check a value in the macro variable list

MichelleHomes — Sat, 29 Jun 2013 23:25:18 GMT

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.

Re: check a value in the macro variable list

Ksharp — Sun, 30 Jun 2013 04:47:01 GMT

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

Re: check a value in the macro variable list

Tom — Mon, 01 Jul 2013 03:30:56 GMT

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.