topic Re: Using Proc Tabulate for more that one table in ODS and Base Reporting

Using Proc Tabulate for more that one table

deleted_user — Fri, 08 Feb 2008 11:01:08 GMT

Hi

I need to perform this query using proc tabulate

select t2.accnum, sum(t1.balance), count(t1.acckey)
from Table1 t1 right join Table2 t2 on t2.acckey = t1.acckey
where t2.accnum in (7,11,14,21)
group by t2.accnum;

All the examples I have found use one table. Please help with proc tabulate with more than 1 table.

Re: Using Proc Tabulate for more than one table

deleted_user — Fri, 08 Feb 2008 11:55:43 GMT

The Tabulate procedure reads a single data set and displays a summary of its contents. Where tables are to be joined and statistics derived, it is usual to use the SQL Procedure with syntax such as you have shown, or a data step using Merge.

The resultant data set is then used as an input to the data reporting step, in this case, Tabulate.

Re: Using Proc Tabulate for more that one table

deleted_user — Fri, 08 Feb 2008 12:11:32 GMT

I am disappointed 😞
I guessed that it possible... I just try to find the fastest way to output this query.
In any case, thank you, because now I can exclude this way and use my time for merging.

Re: Using Proc Tabulate for more that one table

deleted_user — Fri, 08 Feb 2008 14:00:44 GMT

Hippo

if your problem is the large space your data need when joined/merged, remember that data step merges and sql joins can be views.
Then as tabulate (reading the view) runs, the tables are merged into tabulate not disk.

Of course you may have other reasons for wanting tabulate to read more than one table. If so we may be able to help, but it is difficult to understand why until you explain more fully.

PeterC

Re: Using Proc Tabulate for more that one table

deleted_user — Fri, 08 Feb 2008 14:51:50 GMT

Peter_C

It's not strictly to use tabulate.
I just want to know the fastest way to perform this query above, because I have millions of records in each table.

What is the fastest way on your opinion?
It could be great to see example of your solution.

Re: Using Proc Tabulate for more that one table

deleted_user — Fri, 08 Feb 2008 15:12:57 GMT

a good answer needs more information :
data order of each table
any indexes available
size of each table (number of rows)
relative sizes of tables to memory available
relative sizes of acctnum subsets to whole table
why use right(outer) join instead of inner join?
also worth considering is whether there is more use for this join, in addition to this query (sharing "pain" makes it more bear-able)

really, your best option is to hire the time of a good consultant 😉

PeterC