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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.