topic Concatenate datasets within a single macro in SAS Programming

Concatenate datasets within a single macro

West26 — Sun, 12 Mar 2017 17:28:25 GMT

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

Re: Concatenate datasets within a single macro

art297 — Sun, 12 Mar 2017 17:49:38 GMT

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

Re: Concatenate datasets within a single macro

Tom — Sun, 12 Mar 2017 19:10:30 GMT

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)

Re: Concatenate datasets within a single macro

RW9 — Mon, 13 Mar 2017 09:35:36 GMT

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.

Re: Concatenate datasets within a single macro

ballardw — Mon, 13 Mar 2017 15:40:04 GMT

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.

Re: Concatenate datasets within a single macro

West26 — Thu, 16 Mar 2017 22:54:26 GMT

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