Desktop productivity for business analysts and programmers

combine tables

Reply
Contributor
Posts: 66

combine tables

I have 8 datasets to combine to get the reports for a project I am working on

what would be the most efficent way to combine these tables?

is it good to add all 8 tables together in one step or add 2 tables with needed columns first and then add one by one???..

Query runs for 30 minutes with 8 tables in one step

thanks

Super Contributor
Posts: 1,636

Re: combine tables

one step is better.

Contributor
Posts: 66

Re: combine tables

Thank you

Valued Guide
Posts: 2,111

Re: combine tables

In general, I agree with Linlin.  There are some additional things that you can do to potentially make the process faster.  I assume you are using EGuide's query builder task.  If you are doing this as a one-off, then 30 minutes is tolerable; if it is part of a production activity, then it may not be acceptable.

  1. make sure the data are in the order of the join variables first.  That way PROC SQL won't have to re-sort them to do the join.
  2. if you have done other data transformations in previous tasks, make sure you have saved the data set (if it is a VIEW, then SAS must redo the transformations as part of the query builder task.
  3. keep only the variables that you need.
  4. There are other tips here, but you can't necessarily get to them in Query Builder.  http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001360977.htm

Doc Muhlbaier

Duke

Contributor
Posts: 66

Re: combine tables

I believe tables are from production system. I will try first takeing out order by clause

Thanks

Contributor
Posts: 66

Re: combine tables

I just wanted to update this with my experience. It seems query ran faster when tables are added one by one than combining all 8 tables in one step.

Actually ran out of space on work library.

Contributor
Posts: 41

Re: combine tables

Well, from my point of view it's better to hardcode everything.

This will allow you to use certain options that will speed up your merge.

As Doc@Duke mentioned - it's vital for datasets to be sorted by variables which will be the keys for merging in order to decrease calculations AND memory consumption.

Maybe you can just explain your case and we'll see what we can do.

About space on work library. There's an important case when SAS is installed on Windows systems, which has C: drive with several Gb for "System purposes" (with Windows installed on it) and large D: (or any other letter) drive as a main storage appliance. For example, we have IBM blade server which implies this disk partitioning structure: C: 50 Gb D: 500 Gb.

Now, by default SAS assigns WORK library to the folder which is in your "Windows user" folder, which is on drive C:. SAS also use temprorary folders in order to perform sorting and merging (also on CSmiley Happy. So in this case, even if you have datasets in libraries, that placed on a large storage (DSmiley Happy, if you try to merge or sort it SAS will use space on C:.

In order to "heal" this one can change the configuration file a bit. If you have this problem, I can explain you how to fix configuration file.

Contributor
Posts: 66

Re: combine tables

Thanks for sharing info about work library. I am learning more about the tables and it's contents. Gives me some idea, joining  these tables in PROC SQL and reducing the time it takes to run

Ask a Question
Discussion stats
  • 7 replies
  • 551 views
  • 0 likes
  • 4 in conversation