Can anyone help me with the attached sql code -- i&#039;m having efficiency issues(taking way too long to run, and using too much space in the process). I&#039;m thinking the order in which I&#039;ve layed out tables and/or variables at different stages of the code may impact speed/space issues, but I don&#039;t know what the best approach would be.<BR><BR>The product_fact table is the key information table where most of the data needed is coming from. It is also the largest table in the extraction by far. The rest of the tables seen in the from statement are ordered from smallest table(hour) to largest table(member). My where clause is similarly ordered, with my rules input(which has many &#039;rules&#039; based on various variables from some or all of the included tables) at the end.<BR><BR>If anyone out there can lend me a hand with this, I&#039;d be most grateful. Thanks in advance.<BR><BR>/*Text submitted to run*/<BR><BR>rsubmit;<BR>%include &#039;/sas_macros/;; /*code for this is below*/<BR>/* the following is a list of all variables to keep from extraction */<BR>%let ivars= mvmtnum uom qty weight retail sellrtl ntntcost store buyc cat subc item <BR> house cardnum upc posdept desceng day time endweek; <BR>/* these rules are restrictions on extraction */<BR>%let irules= ((acct_qtr in (&#039;2001Q4&#039;)) /*from mb_date table*/<BR> and (cat in (&#039;M1004&#039;)) /*from product table*/<BR> and (ia_household_lvl_cat in (&#039;Household&#039;))); /*from household table */<BR>%item_extract(vars=&ivars, rules=&irules); /*macro call to code below */<BR>endrsubmit;<BR><BR>/* macro code */<BR><BR>%macro item_extract(vars=, rules=);<BR>proc sql;<BR> create table extract(keep=&vars) as<BR> select pf.unique_trans_num as mvmtnum<BR> , pf.item_unit_of_measure as uom<BR> , pf.item_qty as qty<BR> , pf.item_weight as weight<BR> , pf.item_retail as retail<BR> , pf.item_selling_retail as sellrtl<BR> , pf.item_net_net_cost as ntntcost<BR> , pf.transaction_date as day format date9.<BR> , h.ia_hour_desc_eng as time<BR> , s.store_number as store<BR> , s.district as buyc<BR> , fh.pos_dept_number as posdept<BR> , md.week_end_date as endweek<BR> , p.item_eng_desc as desceng<BR> , p.mc1_category_number as cat<BR> , p.mc_sub_category_number as subc<BR> , p.article_number as item<BR> , p.upc_number as upc<BR> , hh.household_number as house<BR> , m.member_card_number as cardnum <BR> from db2lib.product_fact pf<BR> , db2lib.hour h<BR> , s<BR> , db2lib.finance_hier fh <BR> , db2lib.mb_date md<BR> , db2lib.product p<BR> , db2lib.household hh<BR> , db2lib.member m <BR> where pf.hour_id = h.hour_id<BR> and pf.store_id = s.store_id <BR> and pf.fin_hier_id = fh.fin_hier_id<BR> and pf.date_id = md.date_id <BR> and pf.product_id = p.product_id<BR> and pf.household_id = hh.household_id<BR> and pf.member_id = m.member_id<BR> and &rules; <BR>quit;<BR>%mend item_extract;<BR><BR>/* end of macro code */