BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
5 REPLIES 5
deleted_user
Not applicable
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.
deleted_user
Not applicable
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.
deleted_user
Not applicable
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
deleted_user
Not applicable
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.
deleted_user
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 751 views
  • 0 likes
  • 1 in conversation