Joining dozens of tables with a macro

[Xamius32]

So I am trying to combine a bunch of tables into one based on certain conditions. I am not sure how I would write a macro or do loop for it so I dont have to repeat the union step for each table.

This is what I have so far, and would need to do for ~80 tables

create table test as select monthend, loan_no, balance, portfolio, DELQIND180 from lp.lp_200512

where DELQIND180 ne "0" and portfolio="CORE" or portfolio="NAT"

union

select monthend, loan_no, balance, portfolio,  DELQIND180 from lp.lp_200601

where DELQIND180 ne "0" and portfolio="CORE" or portfolio="NAT"

union

select monthend, loan_no, balance, portfolio,  DELQIND180 from lp.lp_200602

where DELQIND180 ne "0" and portfolio="CORE" or portfolio="NAT";

quit;

[yeshwanth]

Hello,

Try this...

Data final;

set lp_200512 - lp_200600; // ( Specify the Range here )

run;

// This prog will append all the data sets from lp_200512 to lp_200600..

[Xamius32]

I forgot to mention, it is by month/year, so its 200512, 200601, 200602...200612, 200701, etc.

[yeshwanth]

Okey,

Try this

Data final;

set lp_200512 -- lp_200712; // ( Specify "--" will append all the data sets which are between 200512 to 200712)

run;

[Astounding]

If you want to combine ALL the data set names that have the right naming pattern, you could use:

data want;

   set ip.ip_20: ;

   where ...;

run;

The WHERE statement would be applied to all the data sets.