topic Re: Joining dozens of tables with a macro in SAS Programming

Joining dozens of tables with a macro

Xamius32 — Thu, 06 Jun 2013 19:44:11 GMT

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;

Re: Joining dozens of tables with a macro

yeshwanth — Thu, 06 Jun 2013 19:48:14 GMT

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

Re: Joining dozens of tables with a macro

Xamius32 — Thu, 06 Jun 2013 19:55:00 GMT

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

Re: Joining dozens of tables with a macro

Astounding — Thu, 06 Jun 2013 20:07:28 GMT

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.

Re: Joining dozens of tables with a macro

yeshwanth — Thu, 06 Jun 2013 20:08:53 GMT

Okey,

Try this

Data final;

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

run;