topic How to select all in a SAS Macro in SAS Programming

How to select all in a SAS Macro

teg_76 — Wed, 02 Oct 2013 16:37:32 GMT

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

Re: How to select all in a SAS Macro

Tom — Wed, 02 Oct 2013 17:46:20 GMT

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;

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

...

where &subgrp

...

Re: How to select all in a SAS Macro

teg_76 — Wed, 02 Oct 2013 22:31:38 GMT

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

Re: How to select all in a SAS Macro

Florent — Tue, 08 Oct 2013 14:43:20 GMT

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