Date:         Fri, 2 Jun 2006 17:08:54 -0400
Reply-To:     Chang Chung <chang_y_chung@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Chang Chung <chang_y_chung@HOTMAIL.COM>
Subject:      Re: Direct references to a variable based on value of another
              variable
Comments: To: Muthia Kachirayan <muthia.kachirayan@GMAIL.COM>

On Fri, 2 Jun 2006 16:33:21 -0400, Muthia Kachirayan
<muthia.kachirayan@GMAIL.COM> wrote:

>Dan,
>
>Here is another solution without using PROC MERGE.
>
>data one ;
> input code unit quantity ;
> cards ;
>235 1 10
>235 2 20
>235 11 30
>240 1  100
>240 11 200
>300 11 200
>run ;
>
>data two ;
> input code cf1 cf2 cf11 ;
> cards ;
>235 1.75 2.5 3.25
>240 2.0  2.5 3.0
>300 3.0  4.2 5.5
>run ;
>
>data a;
>    set one;
>        do i =1 to n;
>            set two (rename=(code = cod)) nobs = n point = i;
>            array c[3] cf1 cf2 cf11;
>            if code = cod then do;
>                if unit = 11 then k = 3;
>                else k = unit;
>                total = quantity * c[k];
>                output;
>                leave;
>            end;
>        end;
>    drop cod k;
>run;
>
Hi, Muthia,

Nice idea. But your do i loop is doing the linear search and can be quite
expensive. You are using set with point=. Why not index the lookup table
(dataset two) on code and use set with key=, instead. Something like below.
You can also add a line right below the second set so that you can check
_IORC_ automatic variable.

Cheers,
Chang

data one ;
  input code unit quantity;
cards ;
235 1  2500
235 2  456
235 11 57
345 7  346
345 8  465
;
run ;

data two(index=(code)) ;
  input code cf1 cf2 cf11;
cards ;
100 1 3 4
235 2 3 3.75
;
run ;

data three;
  set one;
  set two key = code/unique;
  /* TO DO: check _IORC_ here */
  array c[3] cf1 cf2 cf11;
  if unit in (1 2 11) then do;
    total = quantity * c[ifn(unit=11, 3, unit)];
  end; else do;
    total = .;
  end;
run;

proc print data=three noobs;
run;
/* on lst
code    unit    quantity    cf1    cf2    cf11     total

 235      1       2500       2      3     3.75    5000.00
 235      2        456       2      3     3.75    1368.00
 235     11         57       2      3     3.75     213.75
 345      7        346       2      3     3.75        .
 345      8        465       2      3     3.75        .
*/