BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
West26
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
art297
Opal | Level 21

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
Obsidian | Level 7
Sorry that I couldn't back this long. Thank you all for the your time.
Tom
Super User Tom
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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