DATA Step, Macro, Functions and more

Joining dozens of tables with a macro

Reply
Frequent Contributor
Posts: 82

Joining dozens of tables with a macro

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;

Contributor
Posts: 22

Re: Joining dozens of tables with a macro

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

Frequent Contributor
Posts: 82

Re: Joining dozens of tables with a macro

Posted in reply to yeshwanth

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

Contributor
Posts: 22

Re: Joining dozens of tables with a macro

Okey,

Try this

Data final;

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

run;

Super User
Posts: 5,498

Re: Joining dozens of tables with a macro

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.

Ask a Question
Discussion stats
  • 4 replies
  • 168 views
  • 0 likes
  • 3 in conversation