topic Re: How to create a lookup table from all possible group combination in a row in SAS Programming

How to create a lookup table from all possible group combination in a row

bibbnd — Fri, 24 Dec 2021 22:12:44 GMT

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;

Re: How to create a lookup table from all possible group combination in a row

ChrisNZ — Thu, 23 Dec 2021 05:56:28 GMT

I want to create all possible combinations from a row but selecting each rate as the current rate and than selecting the remaining rates in order from smallest to largests. I need to exclude the A or T rates when the current rate is going to be a D , 49, or 58. here is my original dataset group rate1 rate2 rate3 rate4 rate5 rate6 amt1 amt2 amt3 amt4 amt5 amt6 NNN T P 58 49 D A 2002 554 499 498 484 313 NNY T P D 49 58 A 1222 900 800 800 750 610 outcome dataset need: GROUP CURRENT RATE CURRENT AMT RATE1 AMT1 RATE2 AMT2 RATE3 AMT3 RATE4 AMT4 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 data test2; set test; array rates[*] $12 rate1 - Rate6; array k[*] $12 new_rate1 new_Rate2 new_rate3 new_RATE4 new_RATE5 new_rate6; array ramt[*] amt1 - amt6; array j[*] ramt1- ramt6; 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;

Can you reformat your post please? Don't forget to use the appropriate icon for your code.

And show the expected output.

Re: How to create a lookup table from all possible group combination in a row

PeterClemmensen — Thu, 23 Dec 2021 07:02:39 GMT

@bibbnd , I edited your sample data to be more eaily read.

Please explain your desired logic from there 🙂

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 800 750 610 
;

data want;
input GROUP $ CURRENT_RATE $ CURRENT_AMT RATE1 $ AMT1 RATE2 $ AMT2 RATE3 $ AMT3 RATE4 $ AMT4;
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 
;

Re: How to create a lookup table from all possible group combination in a row

bibbnd — Thu, 23 Dec 2021 08:32:14 GMT

I need to logic to create the desired output. I want to select each rate as the current rate and than select the remaining rates from lowest to highest in that same row . Repeat this process within each row for each group until all rates have been given the current rate position. Once this is done, I do it again for the next group.

Not sure why my sample data didn’t appear lined up.

Re: How to create a lookup table from all possible group combination in a row

bibbnd — Thu, 23 Dec 2021 08:34:14 GMT

Didn’t know it posted in this manner.
What do you mean appropriate icon?
I will fix

Re: How to create a lookup table from all possible group combination in a row

Tom — Thu, 23 Dec 2021 16:30:35 GMT

The forum editor has a tool bar with icons.

There is one for inserting text (Insert Code) and one for inserting SAS code (Insert SAS Code). Use one of those buttons to get a pop-up window where you can paste (or edit) the lines of text or code you want to post. Otherwise the forum considers what you type as words to be formatted into paragraphs.

Re: How to create a lookup table from all possible group combination in a row

bibbnd — Thu, 23 Dec 2021 16:46:45 GMT

I don't get any of those options when posting. I can't even edit the post to make it readable.

Re: How to create a lookup table from all possible group combination in a row

Tom — Thu, 23 Dec 2021 17:05:57 GMT

What browser are you using to post your question? Your replies? You should be typing into a box in the middle of the page. Just above the box where you type is the menu bar. Like this:

To edit an old post you can click on the three vertical dots under your icon at the top of the post and select EDIT.

Re: How to create a lookup table from all possible group combination in a row

bibbnd — Thu, 23 Dec 2021 17:45:04 GMT

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 
;

the reformatted version of my dataset is wrong, there is blanks and should not be there, the above code is the correct version of what i need

Re: How to create a lookup table from all possible group combination in a row

AMSAS — Fri, 24 Dec 2021 11:41:59 GMT

@bibbnd a few questions:

The want dataset code you supplied has more input variables (13) than values on your datalines (11).
Please correct this as your want dataset looks like this (lower dataset):
Can you add the NNY group to the want dataset. It might help us understand the logic that you are applying to transform the have dataset to the want dataset
What is the rules/logic you apply to transform the have dataset into the want dataset
For example, why is the first observation in the want dataset Current Rate D (D/484). it is neither the lowest rate (A/313) or Rate1 (T/2002)

Re: How to create a lookup table from all possible group combination in a row

bibbnd — Fri, 24 Dec 2021 22:18:21 GMT

@AMSAS

1. I have fixed the want dataset.

2. Add the the next group to the want dataset.

3. I rewrote my explanation in the first post to better layout the selection process. There is no particular reason in setting the current rate. I just need each of the 6 rates to have a turn at being the current rate to create the 6 rows with each group.

AS I stated, i have been doing this manually for a long time because i have no luck at getting it coded.

Re: How to create a lookup table from all possible group combination in a row

Patrick — Sun, 26 Dec 2021 01:50:38 GMT

@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
;