I will try to better explain what i need to do: there are the following rate categores D, P, T , A , 48,and 59. They are not chosen in any particular order to be the current rate. I just need all of them to be set to the current rate within a row 1 at a time. I need to create a matrix where each one of the rates are set to be the current rate with each different group . It doesn't matter which one is selected first or second to start the process. I pick one of them to be the current rate from a row. After the current rate is set, i than assign the D, 48, or 59 rates in order from smallest corresponding amt to largest corresponding amt within the same row. i don't use the A or T rates in this process. They are only used to be a current rate. I repeat this process with a row until each of the rates have been set to the current rate. Therefore, i will create a total of 6 rows from 1 row by setting each one of the rates to the current rate. I start the process over with the next group once all rates have been set to the current rate. You only see 3 additional rates options on the HAVE dataset when the current rate is a D, 48, or 59. NOTE: I HAVE EDITED MY ORIGINAL POST TO MAKE IT CLEARER AND INCLUDE SUGGESTIONS FROM BELOW. 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;
input GROUP $ CURRENT_RATE $ CURRENT_AMT 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 ; THIS CODE WAS GIVEN TO ME TO EDIT. i HAVE NO IDEA HOW TO MAKE IT WORK. i HAVE BEEN DOING THIS PROCESS MANUALLY IN EXCEL BUT IT NEEDS TO BE AUTOMATED SINCE THE MATRIX REQUIRES MORE FREQUENT UPDATES. data want;
set have;
array rates[*] $12 rate1 - Rate6;
array k[*] $12 current_rate new_rate1 new_Rate2 new_rate3 new_RATE4 new_RATE5;
array ramt[*] amt1 - amt6; array j[*] current_amt ramt1- ramt5;
do i=1 to dim(rates);
current_rate=rates{i];
current_rate_amt=ramt[i};
ind=i+1;
if rates[i]=current and rates[i] in ('A','T') then skip -- not sure how to do that???
do;
k[_ind]=rates[i];
j{_ind}=ramt[i];
end;
else
do;
k{i}=rates{i};
j{i}=ramt[i];
end;
end;
do i=1 to dim(j);
if j[i]=. then j[i]=0;
end;
drop i;
run;
... View more
