Macros - Create table


Macros - Create table

Hi - I have a very large chunk of code that accumulates data from relational tables over the past 25 months worth of data. I wrote the code to test against 6 months data which works fine. When I run it against 25 I get a message saying "run out of spool space".

While there may well be inefficiencies in my code, it does work and I don't have time to rewrite and get the output I need.

What I was hoping to do is to use macro's to parameterize the code such that I could run 1 months worth of data at once and then join the tables together at the end.

The code goes something like this:

create table myDB.report_data1 as (

select .....

from myDB.BigTable a

left join... (select....

from) b

on a.member_id = b.member_id
left join... (select....

from) c

on a.member_id = c.member_id

I have a feeling that it isn't poss to "create table" in TD macro's... is this correct?

If so, does anyone have any ideas as to how I can go about running code for all 25 months?



Re: Macros - Create table

You can do a Create Table as long as it is the last statement in the macro.
Not really sure why you want to do it as a macro though - why not just create the 25 tables (one for each month) in a bteq script.

If you are thinking of parameterising the macro call with a tablename - forget it. The macro is parsed at macro creation time, so you could pass in a parameter of the month, but you cannot parameterise the Report_Data1, 2, ...

You could parameterise these in Unix/ Linux, calling a standard bteq script 25 times with date and tablenames as parameters for shell substitution.

Alternatively, create the 25 tables once and delete/ insert them in a macro.

Do not try to populate all 25 tables in a single macro call - that runs as a single transaction and you will probably run out of spool again.

Re: Macros - Create table

Hi - Thanks for the reply. I don't know what BTEQ is but will have a look into it.

Was also thinking of trying to do this through SAS.