Concatenate datasets within a single macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Concatenate datasets within a single macro

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

 


Accepted Solutions
Solution
‎03-16-2017 06:52 PM
Esteemed Advisor
Posts: 7,299

Re: Concatenate datasets within a single macro

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

 

View solution in original post


All Replies
Solution
‎03-16-2017 06:52 PM
Esteemed Advisor
Posts: 7,299

Re: Concatenate datasets within a single macro

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

 

Occasional Contributor
Posts: 15

Re: Concatenate datasets within a single macro

Sorry that I couldn't back this long. Thank you all for the your time.
Super User
Super User
Posts: 6,368

Re: Concatenate datasets within a single macro

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)
Esteemed Advisor
Esteemed Advisor
Posts: 7,249

Re: Concatenate datasets within a single macro

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. 

Grand Advisor
Posts: 10,238

Re: Concatenate datasets within a single macro

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 190 views
  • 3 likes
  • 5 in conversation