Hi Jamie,
I have modified the code. We have a better solution by PAzevedo.
/* add variable new_quote as a 5% price reduction for all products */
data have;
input CUSTOMERID :QUOTENO :PRODUCTID :QUOTE :rank;
cards;
5000 1 17 53.24 1
5000 1 18 55.27 2
5000 1 19 60.67 3
5000 2 17 44.96 1
5000 2 19 45.17 2
5000 2 20 49.27 3
6050 1 17 72.65 1
6050 1 19 77.45 2
;
run;
data have1;
set have;
new_quote=quote*.95;
run;
proc transpose data=have1 out=look (drop=_name_ ) prefix=original_rank;
by CUSTOMERID QUOTENO;
var new_QUOTE;
run;
data want;
merge have1 look;
by CUSTOMERID QUOTENO;
run;
data want (keep=CUSTOMERID QUOTENO PRODUCTID QUOTE:rank);
set want;
quote1=quote;
quote2=quote;
quote3=quote;
if rank=1 then quote1=original_rank1;
if rank=2 then quote2=original_rank2;
if original_rank2=. then quote2=.;
if rank=3 then quote3=original_rank3;
if original_rank3=. then quote3=.;
run;
data quote1;
set want(drop=quote2 quote3);
proc sort;
by CUSTOMERID QUOTENO QUOTE1;
data quote1;
set quote1;
by CUSTOMERID QUOTENO;
if first.QUOTENO then rank1=1;
output;
rank1+1;
proc sort;
by CUSTOMERID QUOTENO rank;
run;
/***********************************/
data quote2;
set want(drop=quote1 quote3);
proc sort;
by CUSTOMERID QUOTENO QUOTE2;
data quote2;
set quote2;
by CUSTOMERID QUOTENO;
if first.QUOTENO then rank2=1;
output;
rank2+1;
proc sort ;
by CUSTOMERID QUOTENO rank;
run;
/***********************************/
data quote3;
set want(drop=quote2 quote1);
proc sort;
by CUSTOMERID QUOTENO QUOTE3;
data quote3;
set quote3;
by CUSTOMERID QUOTENO;
if first.QUOTENO then rank3=1;
output;
if quote3=. then rank3=.; else rank3+1;
proc sort;
by CUSTOMERID QUOTENO rank;
run;
data final(drop=new_quote);
set quote1(rename=(quote1=new_quote rank1=new_rank))
quote2(rename=(quote2=new_quote rank2=new_rank))
quote3 (rename=(quote3=new_quote rank3=new_rank)) ;
if new_quote=. then delete;
if quote=new_quote then delete;
run;
proc sort ;
by CUSTOMERID QUOTENO rank ;
proc print;run;
5% reduction
Obs CUSTOMERID QUOTENO PRODUCTID QUOTE rank new_rank
1 5000 1 17 53.24 1 1
2 5000 1 18 55.27 2 1
3 5000 1 19 60.67 3 3
4 5000 2 17 44.96 1 1
5 5000 2 19 45.17 2 1
6 5000 2 20 49.27 3 3
7 6050 1 17 72.65 1 1
8 6050 1 19 77.45 2 2
