DATA Step, Macro, Functions and more

How to select all in a SAS Macro

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

How to select all in a SAS Macro


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


Accepted Solutions
Solution
‎10-02-2013 01:46 PM
Super User
Super User
Posts: 6,499

Re: How to select all in a SAS Macro

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

...

View solution in original post


All Replies
Solution
‎10-02-2013 01:46 PM
Super User
Super User
Posts: 6,499

Re: How to select all in a SAS Macro

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

...

Contributor
Posts: 41

Re: How to select all in a SAS Macro

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

Frequent Contributor
Posts: 127

Re: How to select all in a SAS Macro

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 311 views
  • 0 likes
  • 3 in conversation