Tucked in the SAS Enterprise Guide Query Builder there is a text box unhelpfully labelled 'Options'. To find it select Options -> Options for this query -> General, and it is about halfway down the screen.

I am going to show you how to use that text box to make your tables smaller, and how to create tables ready for faster access.

First of all, what are these options? Well, in SAS programming when you refer to a table (Data Set) you can follow the table name with a list of options enclosed in brackets. It works consistently everywhere, including PROC SQL.

Entering Data Set Options in the Options box adds them to the PROC SQL generated by the query builder.

Making Tables Smaller

If a table is smaller it will take up less valuable storage space, and it will take less valuable time to read.

This will work best on tables with character columns containing lots of repeated characters, typically spaces. In the Options box type in the option COMPRESS=YES. After you have run the Query look in the log and you will see how much you have saved. If nothing has been saved, or it has increased the size, which can happen, then remove the option and re-run.

There may be a small increase in CPU, but the disc IO savings should outweigh that.

With other types of data try COMPRESS=BINARY. This will work better with numeric columns, large rows, or data with repeated patterns. Again a quick look at the log will tell you if the compression has worked.

It is possible to set compression as a system or library option. If you look at the log and you see it is compressing without you asking for it you can turn it off with COMPRESS=NO.

Getting Ready for Faster Access

When you are reading a non-fiction book you will often find an index at the back which enables you to go straight to a page or pages to find out about a particular subject. Really useful for finding small bits of information from a large volume.

When you are creating tables in SAS you can get SAS to create an index to help find your data quickly. As a rule of thumb the smaller the amount of data you want to get, and the larger the table, the more likely it is that an index will be used. Typically an index might be used if you are filtering your data or if you are joining tables together.

In the options box simply type INDEX=(columnname) where columnname is name of the column you want an index built on. For example INDEX=(CUST_ID) would build and index on cust_id column. If in a later task you filter on cust_id, or join to the table via cust_id, it should be faster.

You can create indices on more than one column, either individually or combined into a composite index.

You can combine compression and indexing in the same table just type both options in the Options box.

To find out about more advanced indexing and to see a list of other options go to support.sas.com. They are not all still useful, some refer to tape, but there are some interesting things to try.

I hope that you find this tip useful. You will find more details on using 'options' in SAS Enterprise Guide in my book, SAS® Programming for Enterprise Guide® Users, Second Edition.