The SAS Output Delivery System and reporting techniques

Using Proc Tabulate for more that one table

Reply
N/A
Posts: 0

Using Proc Tabulate for more that one table

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.
N/A
Posts: 0

Re: Using Proc Tabulate for more than one table

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: Using Proc Tabulate for more that one table

Posted in reply to deleted_user
I am disappointed Smiley Sad
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.
N/A
Posts: 0

Re: Using Proc Tabulate for more that one table

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Using Proc Tabulate for more that one table

Posted in reply to deleted_user
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.
N/A
Posts: 0

Re: Using Proc Tabulate for more that one table

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 5 replies
  • 183 views
  • 0 likes
  • 1 in conversation