How to select all in a SAS Macro

[teg_76]

Hi Everyone,

I have a SAS Macro that looks like this:

%LET SUBGRP=('202')

and I'm using it in a proc SQL statement:

cm.subgrp in @SUBGRP

This works, but what if I want to allow the user of the program to select all subgroups?  Sometimes they will need to do that.  How would I adjust my macro?

I tried:

%LET SUBGRP=('*')

and

%LET SUBGRP=('%')

..and both of those didn't work.  Any suggestions?

Thanks,

Tom

[Tom · Accepted solution]

Not really possible in PROC SQL to use both the IN operator and some type of wildcard.

If you are actually inside of a macro so that you can use macro logic then you could conditionally generate different SQL based on the value of your macro variable.  For example if you decide to use %let subgrp=*; to mean select all then you could code it like this:

...

where

%if "&subgrp" = "*" then 1 ;

%else cm.subgrp in &subgrp ;

...

Otherwise you could place the whole conditional into the macro variable.

%let subgrp=1;

or

%let subgrp=cm.subgrp in ('22','33');

...

where &subgrp

...

[teg_76]

Thanks for your help Tom!  I'll use your suggestions and see if I can work around the problem...

[Florent]

You could also update the proc SQL with something like this:

%LET SUBGRP = ;

Proc sql;

....

%IF &SUBGRP ne %str() %THEN %DO;

   where cm.subgrp in @SUBGRP

   ...

%END; %ELSE %DO;

   ....

%END;

...;

quit;

This would check whether a value was assigned to your macro and, in such a case, add the where condition to your sql query.

Regards,

Florent