2 guaranteed stumpers

Results 1 to 2 of 2

Thread: 2 guaranteed stumpers

  1. #1
    Join Date
    Dec 1969

    Default 2 guaranteed stumpers

    Here are some guaranteed stumpers that no one has been able to answer -- i challenge anyone to resolve these!<BR><BR>Stumper #1<BR><BR>SQL 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>/*Text submitted to run*/ <BR><BR>rsubmit; <BR>%include &#039;/sas_macros/macros.sas&#039;; /*macro code for this can be found after this section of code*/ <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>, db2lib.store 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 */ <BR><BR><BR><BR>Stumper #2<BR><BR>any idea what the _method does when added to the proc sql line at the beginning of any sql code? <BR>(e.g. proc sql _method; ...)<BR>

  2. #2
    zxcv Guest

    Default i doubt anyone could answer these!

    i guess nobody could solve them -- not surprised, they are pretty tough -- better luck next time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts