topic Re: Find two best rates from list of 6 variables and corresponding amounts to match in SAS Programming

Find two best rates from list of 6 variables and corresponding amounts to match

bibbnd — Thu, 17 Jun 2021 01:41:14 GMT

I need two search a series of 7 variables for the best rate based on the character string of a 4D or 5D. If the best rate has these values then i need to capture their corresponding rate values also. So eventually i want to keep the rates with 4D or 5D and their rate_amts in new fields such as rateA, rateA_amt and rateB, rateB_amt

i started the code like this but not sure how to proceed

data test;

set vgrid;

ARRAY RATE[*] $18 RCOMPARED1-RCOMPARED7;

array rateA[*] $18;

array rateB[*] $18;

array rateA_amt[*] 10.2;

array rateB_amt[*] 10.2;

DO i to dim;

if index(rate(i), '4D') > 0 OR index(rate(i), '5D') > 0 THEN DO;

*this is where i am not sure what to do next to capture only those rates and their corresponding amts to output

end;

run;

Re: Find two best rates from list of 6 variables and corresponding amounts to match

ballardw — Thu, 17 Jun 2021 03:57:02 GMT

Some sort of complete data might help. And then what the result would be.

If your intent with the Rate arrays was to create multiple variables then your syntax is incorrect. If you intend to reference existing variables you need to name them. If you are creating an array of new variables you have to specify how many variables.

Array RateA (7) ;

would create 7 variables named RateA1, RateA2, ....,RateA7.

If you want to know which element of an array holds a specific value then you use the WHICHC, for character variables, or WHICHN for numeric:

Vnum = whichc('4D', of rate(*));

If Vnum > 0 then somevalue = nameofarray[Vnum];

would return the first position that '4D' is found in the Rate array and then use that value to find the corresponding value in another array.

Details come in with what to do when '4D' or '5D' repeat or are both found in a single observation.

Which is why a more complete example of data with ALL the variables and what you expect the result to be.

Re: Find two best rates from list of 6 variables and corresponding amounts to match

Patrick — Thu, 17 Jun 2021 04:36:06 GMT

Post representative sample data provided via a SAS data step creating the data and show us the expected result using this data.

Re: Find two best rates from list of 6 variables and corresponding amounts to match

bibbnd — Thu, 17 Jun 2021 20:30:33 GMT

i figured it out

Re: Find two best rates from list of 6 variables and corresponding amounts to match

ballardw — Sat, 19 Jun 2021 07:55:03 GMT

@bibbnd wrote:

i figured it out

Good job.

Now post your solution so if someone finds this thread in a search for similar problems they can find your solution.

After posting your solution mark it as the accepted solution so people will know there was a solution.

Re: Find two best rates from list of 6 variables and corresponding amounts to match

bibbnd — Mon, 21 Jun 2021 23:55:53 GMT

data test;

set rates_all;

Format new_best_rate other_rate $18.;

array rate[*] $18 current_rate best_rate ratecompared1 - ratecompared5;

array ramt[*] current_amt bestrate_amt ratecompared_amt1 - ratecompared_amt5;

*intializing new variables;

new_best_rate=' ';

new_bestrate_amt=0;

new_savings=0;

other_rate=' ';

other_amt=0;

do i= 1 to dim(rate);

if indexc(rate{i},'4') > 0 OR indexc(rate{i},'5') > 0 then do;

if new_bestrate_amt ne 0 then do ;

other_rate=rate{i};

other_amt=ramt{i};

end;

else do;

new_best_rate = rate{i};

new_bestrate_amt = ramt{i};

new_savings=(current_amt-ramt{i});

end;

END;