@bibbnd
Below one way to go based on how I understand what you need. The code doesn't exactly create your desired outcome. I made the assumption this is due to some "typo" on your side when creating this data.
data have;
input group $ (rate1 - rate6)($) amt1 - amt6;
datalines;
NNN T P 58 49 D A 2002 554 499 498 484 313
NNY T P D 49 58 A 1222 900 800 801 750 610
;
data want;
if _n_=1 then
do;
if 0 then set have(keep=group rate1 amt1 rename=(rate1=rate amt1=amt));
dcl hash h1(ordered:'y',multidata:'y');
h1.defineKey('amt');
h1.defineData('group', 'rate', 'amt');
h1.defineDone();
end;
call missing(of _all_);
set have;
/* load array elements into hash */
/* - creates a long data structure sorted by amount */
array a_rate {*} rate1 - rate6;
array a_amt {*} amt1 - amt6;
do _i=1 to dim(a_rate);
rate=a_rate[_i];
amt=a_amt[_i];
h1.add();
end;
/* read sorted data from hash into array and create permutations */
dcl hiter hh1('h1');
do _perm=1 to dim(a_rate);
call missing(of a_rate[*], of a_amt[*]);
_el=1;
hh1.first();
do _i=1 to dim(a_rate);
if _i=_perm then
do;
a_rate[1]=rate;
a_amt[1]=amt;
end;
else
if rate not in ('A','T') then
do;
_el+1;
a_rate[_el]=rate;
a_amt[_el]=amt;
end;
if _i<dim(a_rate) then hh1.next();
end;
output;
end;
hh1.delete();
h1.clear();
drop rate amt _: ;
run;
data want;
/* create desired column names and column order */
length
group $8
rate1 $8 amt1 8
rate2 $8 amt2 8
rate3 $8 amt3 8
rate4 $8 amt4 8
rate5 $8 amt5 8
rate6 $8 amt6 8
;
rename
rate1=current_rate amt1=current_amt
rate2=rate1 amt2=amt1
rate3=rate2 amt3=amt2
rate4=rate3 amt4=amt3
rate5=rate4 amt5=amt4
rate6=rate5 amt6=amt5
;
set want;
run;
data desired;
infile datalines truncover dsd;
input GROUP $ CURRENTrate $ CURRENTamt RATE1 $ AMT1 RATE2 $ AMT2 RATE3 $ AMT3 RATE4 $ AMT4 RATE5 $ AMT5;
infile datalines dlm = ',';
datalines;
NNN,D,484,49,498,58,498,P,554
NNN,49,498,D,484,58,499,P,554
NNN,58,499,D,484,49,498,P,554
NNN,P,554,D,484,49,498,58,499
NNN,A,610,D,484,49,498,58,499,P,554
NNN,T,554,D,484,49,498,58,499,P,554
NNY,T,1222,58,750,D,800,49,800,P,900
NNY,P,900,58,750,D,800,49,801
NNY,D,800,58,750,49,801,P,900
NNY,49,801,58,750,D,800,P,900
NNY,58,750,D,800,49,801,P,901
NNY,A,610,58,750,D,800,49,801,P,900
;
... View more
