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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.