We have contracts that range from 0 to 2 billion dollars.  We get
 proposals that apply a sliding fee based on the size of the contract.
 a typical proposal might look like

                  50 basis points on first 200 million
                  40 basis points on next 300 million
                  30 basis points on next 500 million
                  20 basis points on next 1000 million

 all the proposals go from 0 to 2 billion dollars

 I've created a dataset that looks like:

 PROPOSAL      DOL_BRK    BPS_BRK
                   1                  200                 50
                   1                  300                 40
                   1                  500                 30
                   1                1000                 20
                   2                  250                 40
                   2                  250                 30
                   2                1500                 15

 The amount and number of breakpoints can be different for each
 proposal.

 I want to create a new dataset that has as the first column a counter
 that goes from 0 to 2000 by 100 and then for columns 2 through N+1
the
 average fee at each level for each proposal.

 this dataset would look like:
 col1          P1                    P2               P3  ....   Pn

  100          50                    40
  200          50                    40
  300          46.666             38.333
  400          45                    36.25
  500          44                    35
    .
    .
    .

The following code works but seems like I'm over using macro
variables.  Is there a cleaner solution?

%macro bid;

proc sql noprint;
select count(*) into: cnt
from sub_adv.master_bid_h;
quit;
%let cnt = &cnt;

proc sql noprint;
select dol_brkpnt into:dol1-:dol&cnt
from sub_adv.master_bid_h;
select bps_brkpnt into:bps1-:bps&cnt
from sub_adv.master_bid_h;
quit;

%let cum0 = 0;
%let cum1 = %sysevalf(&dol1);

%do i = 2 %to &cnt;
%let   j= %eval(&i-1);
%let cum&i = %sysevalf(&&cum&j + &&dol&i);

%end;

%do i = 1 %to 21;
  %let level&i = %eval((&i-1)*100);
%end;

data bid_test;
retain LEVEL AVG_FEE;
if _n_ = 1 then do;
   avg_fee = &bps1;
   level = 0;
   output;
   end;

%do k = 2 %to 81;

        %do j = 1 %to &cnt;

           %if &&level&k <= &&cum&j %then %do;

                 %let m = %eval(&j-1);
                 %let inc = %sysevalf(&&level&k - &&cum&m);
                 avg_fee =  avg_fee*level+%sysevalf(100*&&bps&j))/
&&level&k;
                 level = &&level&k;
                 output;
                %goto NEXT_LEVEL;
           %end;

        %end;
    %NEXT_LEVEL:
%end;
run;

%mend bid;
%bid;

On Dec 9, 5:59=A0pm, "dc...@hotmail.com" <dc...@hotmail.com> wrote:
> We have contracts that range from 0 to 2 billion dollars. =A0We get
> =A0proposals that apply a sliding fee based on the size of the contract.
> =A0a typical proposal might look like
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 50 basis points on first 200 million
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 40 basis points on next 300 million
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 30 basis points on next 500 million
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 20 basis points on next 1000 million
>
> =A0all the proposals go from 0 to 2 billion dollars
>
> =A0I've created a dataset that looks like:
>
> =A0PROPOSAL =A0 =A0 =A0DOL_BRK =A0 =A0BPS_BRK
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0200 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 50
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0300 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 40
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0500 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 30
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01=
000 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 20
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0250 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 40
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0250 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 30
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01=
500 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 15
>
> =A0The amount and number of breakpoints can be different for each
> =A0proposal.
>
> =A0I want to create a new dataset that has as the first column a counter
> =A0that goes from 0 to 2000 by 100 and then for columns 2 through N+1
> the
> =A0average fee at each level for each proposal.
>
> =A0this dataset would look like:
> =A0col1 =A0 =A0 =A0 =A0 =A0P1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0P2 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 P3 =A0.... =A0 Pn
>
> =A0 100 =A0 =A0 =A0 =A0 =A050 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A040
> =A0 200 =A0 =A0 =A0 =A0 =A050 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A040
> =A0 300 =A0 =A0 =A0 =A0 =A046.666 =A0 =A0 =A0 =A0 =A0 =A0 38.333
> =A0 400 =A0 =A0 =A0 =A0 =A045 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A036.2=
5
> =A0 500 =A0 =A0 =A0 =A0 =A044 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A035
> =A0 =A0 .
> =A0 =A0 .
> =A0 =A0 .
>
> The following code works but seems like I'm over using macro
> variables. =A0Is there a cleaner solution?
>
> %macro bid;
>
> proc sql noprint;
> select count(*) into: cnt
> from sub_adv.master_bid_h;
> quit;
> %let cnt =3D &cnt;
>
> proc sql noprint;
> select dol_brkpnt into:dol1-:dol&cnt
> from sub_adv.master_bid_h;
> select bps_brkpnt into:bps1-:bps&cnt
> from sub_adv.master_bid_h;
> quit;
>
> %let cum0 =3D 0;
> %let cum1 =3D %sysevalf(&dol1);
>
> %do i =3D 2 %to &cnt;
> %let =A0 j=3D %eval(&i-1);
> %let cum&i =3D %sysevalf(&&cum&j + &&dol&i);
>
> %end;
>
> %do i =3D 1 %to 21;
> =A0 %let level&i =3D %eval((&i-1)*100);
> %end;
>
> data bid_test;
> retain LEVEL AVG_FEE;
> if _n_ =3D 1 then do;
> =A0 =A0avg_fee =3D &bps1;
> =A0 =A0level =3D 0;
> =A0 =A0output;
> =A0 =A0end;
>
> %do k =3D 2 %to 81;
>
> =A0 =A0 =A0 =A0 %do j =3D 1 %to &cnt;
>
> =A0 =A0 =A0 =A0 =A0 =A0%if &&level&k <=3D &&cum&j %then %do;
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0%let m =3D %eval(&j-1);
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0%let inc =3D %sysevalf(&&level&k - &&c=
um&m);
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0avg_fee =3D =A0avg_fee*level+%sysevalf=
(100*&&bps&j))/
> &&level&k;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0level =3D &&level&k;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0output;
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 %goto NEXT_LEVEL;
> =A0 =A0 =A0 =A0 =A0 =A0%end;
>
> =A0 =A0 =A0 =A0 %end;
> =A0 =A0 %NEXT_LEVEL:
> %end;
> run;
>
> %mend bid;
> %bid;

Well DC353 it's just you and me kid.  Seems like no one really cares
about crappy code.  Here's a cleaner solution, although we still get a
warning from the UNDO_POLICY=3DNONE statement.  Not really sure how to
fix that in sas8.2.  suppressed the log .



%macro bid2;
data sub_adv.bid_test2;
     retain PRO_NUM LEVEL AVG_FEE;
     format PRO_NUM 5. LEVEL 6. AVG_FEE 10.5;
            if pro_num =3D . then delete;
run;

proc printto log=3D'log_file';
run;

proc sql noprint;
select count(*) into: pro_cnt
       from (select distinct pro_num from sub_adv.master_bid_h);
	   quit;
       %let pro_cnt =3D &pro_cnt;

%do i =3D 1 %to &pro_cnt;
proc sql nofeedback UNDO_POLICY=3DNONE;
insert into sub_adv.bid_test2
       (PRO_NUM,
	LEVEL,
	AVG_FEE)
set     PRO_NUM=3D&i,
        LEVEL=3D0,
	AVG_FEE=3D(select max(bps_brkpnt) from sub_adv.master_bid_h
	                                where PRO_NUM =3D &i);
quit;
run;
%do j =3D 25 %to 2000 %by 25;
    proc sql UNDO_POLICY=3DNONE;
	     insert into sub_adv.bid_test2
		   (PRO_NUM,
                    LEVEL,
                    AVG_FEE)
             set    PRO_NUM =3D &i,
                    LEVEL =3D &j,
                    AVG_FEE =3D  ((select min(avg_fee)    from
sub_adv.bid_test2    where pro_num =3D &i)*
                             (select max(level)      from
sub_adv.bid_test2    where pro_num =3D &i) +
                          25*(select bps_brkpnt      from
sub_adv.master_bid_h where pro_num =3D &i and
                 cum_brkpnt =3D(select min(cum_brkpnt) from
sub_adv.master_bid_h where cum_brkpnt >=3D &j
                              and pro_num =3D &i)))/&j;

                 quit;
%end;
%end;

proc printto ;
run;

proc transpose data =3D sub_adv.bid_test2 OUT=3Dsub_adv.bid_test2  ;
     by pro_num;
     var  level avg_fee;
run;
data sub_adv.bid_test2;
set  sub_adv.bid_test2;
     if _n_ > 1 and _name_ =3D 'LEVEL' then delete;
     if _name_ =3D 'LEVEL' then pro_num =3D 0;
run;
proc transpose data =3D sub_adv.bid_test2 OUT=3Dsub_adv.bid_test2
(drop=3D_name_) prefix=3DP ;
     id  pro_num;
     var col1-col81;
run;
data sub_adv.bid_test2;
set  sub_adv.bid_test2;
     format P0 5. P1-P3 10.5;
RUN;

%mend bid2;
%bid2;