Concatenate datasets within a single macro

[West26]

%macro try(dsn=);

proc sql;
create table required as
select *
from &dsn.
where condition=x
quit;

%mend try;

I'm able to get the correct result if I call   %try(dsn=abc)

What if I have few dsn's and concatenate them by using set statement, within the same above macro.

Thak yoiu.

[art297 · Accepted solution]

You could do something like:

data abc;
  type='abc';
  condition=1;
  output;
run;

data def;
  type='def';
  condition=1;
  output;
run;

%macro try(dsn=);
  proc sql;
    create table required as
      select *
        from &dsn.
        %if %sysfunc(countw(&tdsn.)) gt 1 %then %do i=2 %to %sysfunc(countw(&tdsn.));
          union 
            select *
              from %scan(&tdsn.,&i.)
        %end;
  where condition=1
    ;
  quit;
%mend try;

%try(dsn=abc def)

Art, CEO, AnalystFinder.com

[West26]

Sorry that I couldn't back this long. Thank you all for the your time.

[Tom]

It is much easier if you just use a data step instead of trying to use PROC SQL.  

%macro try(dsn=);
data required;
  set &dsn ;
  where condition=x ;
run;
%mend try;

You can then just call it with a space delimited list of dataset names.

%try(dsn=A B C)

[RW9]

Which of course raises the question of why the macro at all as:

%let dsn=abc def;

data required;
  set &dsn.;
  where conditon=x;
run;

Or even:

data required;
  set abc def;
  where conditon=x;
run;

So the given example is pointless obfucating the code. 

[ballardw]

And if all your datasets that you want to process in a library start with the same characters :

Data required;

   set mylib.abc: ;

   where condition=x;

run;

would combine: mylib.abc1 mylib.abc2 mylib.abcpdq;

 any of the sets that start with abc.