Rusty,

You probrably could force SQL to use the indexs but the code might end up being way more complex and uglier or more confusing than what you already have. The SQL optimizer has a few choices and attempts to make the best one, as Harry so wisely advised, if you choose to go down this path you will want to use _tree and _method to look at what the SQL optimizer is choosing. Also there is a great 57 page paper on the SQL optimizer in the SUGI 30 proceddings.

Toby Dunn

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of Rusty Shackleford Sent: Tuesday, April 26, 2005 11:49 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Compound indexes and proc sql?

I want to join two tables using a compound key of mbrno and mm using proc sql. I'm concerned that proc sql is not taking advantage of the indexes I've created and is doing the merge in a slow sort way.

The mem table has one record for each unique combination of mbrno and mm, and the clm table has lots of.

I've created a compound index on the mem table:

proc datasets; modify mem; create index mbrno_mm=(mbrno mm); run;

Here's how I combine the two tables without proc sql:

data matchrate; set clm; set mem key=mbrno_mm / unique;

matches = 1;

if _IORC_ eq %sysrc(_DSENOM) then do; matches = 0; _ERROR_ = 0; end; run;

That's some seriously ugly code, especially with the call to %sysrc.

Is there a way to use proc sql and be sure that my index mbrno_mm is being used?

TIA __________________________________________________________________________________________________________________________________

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par un autre moyen.