COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### overuse of macro variables

• Follow

```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
quit;
%let cnt = &cnt;

proc sql noprint;
select dol_brkpnt into:dol1-:dol&cnt
select bps_brkpnt into:bps1-:bps&cnt
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;
```
 0
Reply dc353 (169) 12/9/2009 10:59:19 PM

```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
> quit;
> %let cnt =3D &cnt;
>
> proc sql noprint;
> select dol_brkpnt into:dol1-:dol&cnt
> select bps_brkpnt into:bps1-:bps&cnt
> 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;
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;
(PRO_NUM,
LEVEL,
AVG_FEE)
set     PRO_NUM=3D&i,
LEVEL=3D0,
where PRO_NUM =3D &i);
quit;
run;
%do j =3D 25 %to 2000 %by 25;
proc sql UNDO_POLICY=3DNONE;
(PRO_NUM,
LEVEL,
AVG_FEE)
set    PRO_NUM =3D &i,
LEVEL =3D &j,
AVG_FEE =3D  ((select min(avg_fee)    from
(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
and pro_num =3D &i)))/&j;

quit;
%end;
%end;

proc printto ;
run;

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

%mend bid2;
%bid2;

```
 0

1 Replies
221 Views

Similiar Articles:

7/15/2012 2:19:09 AM