Help using Base SAS procedures

SAS & Database tables

Reply
Frequent Contributor
Posts: 117

SAS & Database tables

Hi Guys,

I use SAS to pull some tables from an ODBC connection. Just wondering if there is any way to make this process faster from a code side? I already use the compress function and only pull variables I need. Sample code:

proc sql;

connect to   odbc (dsn=&dsn uid=&uid pwd=&pwd);

create table data.table1 as

select       * from connection to odbc

(

select    SHARED.ID,

             SHARED.NAME

from       SHARED.REASON

);

quit;

Are there any other ways to make the pull faster?

Contributor
Posts: 72

Re: SAS & Database tables

Don't know if this will help...

If you already have a subset of ID's that you want from this table, you could just pull those ID's instead of all ID's, for example:

proc sql;
   connect to odbc (dsn=&dsn uid=&uid pwd=&pwd);
   create table data.table1 as
      select * from connection to odbc
        (select SHARED.ID,
                   SHARED.NAME
         from   SHARED.REASON)

         as sql1(ID, NAME)

         inner join data.Subset_Of_IDs     sas1
         on  sql1.ID = sas1.ID;
   disconnect from odbc; 
quit;

Frequent Contributor
Posts: 117

Re: SAS & Database tables

Thanks for the suggestion! I do need the entire table so unfortunately I cannot limit to a subset of IDs.

Super User
Super User
Posts: 6,502

Re: SAS & Database tables

Can you switch to using a direct access to the source database rather than using ODBC?

You should get better performance accessing the data from say an Oracle table using SAS/Access to Oracle.

PROC Star
Posts: 1,099

Re: SAS & Database tables

If your connection is set up to pre-read very few records, bumping that number might help. I got a huge increase once going from pre-reading 10 records to 500. Frequently the pre-read is set low because DBMSs tend to be used for transaction processing, not bulk retrievals.

Tom

Frequent Contributor
Posts: 117

Re: SAS & Database tables

@ Tom

Is this something I set in sas? Or on database end?

If in SAS how and where should this go?

Thanks!

PROC Star
Posts: 1,099

Re: SAS & Database tables

In the situation I had, it was in the ODBC connection. You'll need to check with your DBMS documentation to find the right parameter.

Tom

Super User
Posts: 3,114

Re: SAS & Database tables

Try the READBUFF= option on your ODBC connect statement: READBUFF = 32767. 32767 is usually the maximum setting depending on the database you are reading.

Frequent Contributor
Posts: 117

Re: SAS & Database tables

Where would this go in the code? Would it be:


proc sql;

connect to   odbc (dsn=&dsn uid=&uid pwd=&pwd) READBUFF = 32767 ;

create table data.table1 as

select       * from connection to odbc

(

select    SHARED.ID,

             SHARED.NAME

from       SHARED.REASON

);

quit;

??

Frequent Contributor
Posts: 117

Re: SAS & Database tables

Thanks Tom, can someone please tell me how to use the readbuff in my example query setup?

Super User
Posts: 3,114

Re: SAS & Database tables

Try: connect to odbc (dsn=&dsn uid=&uid pwd=&pwd READBUFF = 32767) ;

Suggest you also check out the relevant SAS help, either in your installation or online, as all this type of information is there at your fingertips! In my experience changing READBUFF can significantly improve read performance.

Ask a Question
Discussion stats
  • 10 replies
  • 584 views
  • 0 likes
  • 5 in conversation