Finding 2 minimum value using array and return column names

[jamerse]

Hi All,

            I need you guys' support to help me figure it out this problem. What I am working on is that I would like to find 2 minimum value in the column called "Offer1 - Offer10" and return the column name with the condition that these 2 minimum values need to be greater than the reference value (called "Current Offer") and in case there is no any value in "Offer1 - Offer10" then return as "No Offer". What I could do now is I could find the minimum value and return the column names but am currently struggling when there is more condition.

Example: No1. => Current Offer is 3 then 2 minimum offer can be 6 and 7.

                 No2. => Current Offer is 4 then 2 minimum offer I would like is 5 and 6.

                 No6. => Current Offer is 5 and no any value in Offer1-Offer10 then return as "No Offer"

                 No8. => Current Offer is 4 and only one value (Offer10) then return as 10.

Thank you very much for all your contribution and support.

Best Regards,

Jame.

[KachiM]

 cur_offer offer1 - offer10;
   5            1 2 3 4 5 6  7  6  6  10

In this case what are the expected values for Best_offer1 and Best_offer2? The first smallest greater than 5 is 6 and so Best_offer1 = 6. The question what would you like to have Best_offer2? Is it 8 or 7?

[jamerse]

Thank you datasp for looking it up. My expected result for Best_Offer1 is 6 and Best_Offer2 is 7.

Thank you.

[KachiM · Accepted solution]

The following steps are followed:

[1] Copy offer1 - offer10 ( array o[ ]) into another _temporary_ array (ORIG[ ]) for lookups.  _temporary_ array will be filled with missing values at the beginning of the data step iteration. Copy to it all values greater than cut_offer at the position as in array offer1 - offer10.

[2] Sort the ORIG[ ] array. This will place all the missing values to the beginning of the array shifting non-missings to the right.

[3] Using WHICHN() function we search for the position of the first and second non-missing values in array o[ ](for Best_offer1 and Best_offer2).

[4] When Best_offer1 = Best_offer2, then search for the next non-missing value and its corresponding position in array o[ ]

I have added some rows for testing purpose.

data have;
input cur_offer offer1 - offer10;
datalines;
3 . . . . . 6  7  8  9  10
4 1 2 . . 5 6  7  8  9  10
4 1 2 . . 9 6  7  8  5  10
2 . . 3 4 5 6  7  8  9  10
4 . . . 4 5 6  7  8  9  10
1 1 2 3 4 5 6  7  8  9  10
5 . . . . . .  .  .  .   .
5 1 2 . . . .  7  8  9  10
4 . . . . . .  .  .  .  10
4 . . . . . .  .  .  .   .                        
9 . . . 3 . 6  .  .  .  10
5 1 2 3 4 5 6  7  6  6  10
;
run;

The program follows:

data want;
   set have;
   array o offer1 - offer10;
   array orig[10] _temporary_;
   call missing(of orig[*]);
   do i = 1 to dim(o);
      if o[i] > cur_offer then orig[i] = o[i];
   end;
   call sortn(of orig[*]);

   Best_offer1 = .; Best_offer2 = .;
   do i = 1 to dim(o);
      if missing(orig[i]) then continue;
      Best_offer1 = whichN(orig[i], of o[*]);
      j = i+1;
      if j <= dim(o) then Best_offer2 = whichN(orig[j], of o[*]);
      do while( j <= dim(o) and Best_offer1 = Best_offer2);
         Best_offer2 = whichN(orig[j], of o[*]);
         j+1;
      end;
      leave;
   end;

drop i j;
run;

If there are any queries or difficulties please seek help.

Best regards

[Ksharp]

data have;
input cur_offer offer1 - offer10;
array x{*} offer1-offer10;
do i=1 to dim(x);
 if x{i}<=cur_offer then x{i}=.;
end;
smallest1=smallest(1,of x{*});
if not missing(smallest1) then smallest1_vname=vname(x{whichn(smallest1,of x{*})});
smallest2=smallest(2,of x{*});
if not missing(smallest2) then smallest2_vname=vname(x{whichn(smallest2,of x{*})});
datalines;
3 . . . . . 6  7  8  9  10
4 1 2 . . 5 6  7  8  9  10
4 1 2 . . 9 6  7  8  5  10
2 . . 3 4 5 6  7  8  9  10
4 . . . 4 5 6  7  8  9  10
1 1 2 3 4 5 6  7  8  9  10
5 . . . . . .  .  .  .   .
5 1 2 . . . .  7  8  9  10
4 . . . . . .  .  .  .  10
4 . . . . . .  .  .  .   .                        
9 . . . 3 . 6  .  .  .  10
5 1 2 3 4 5 6  7  6  6  10
;
run;

[KachiM]

The labeling part can be done as in:

data want;
   set have;
   array o offer1 - offer10;
   array label[10] $8 _temporary_ ('offer1' 'offer2' 'offer3' 'offer4' 'offer5'
                                   'offer6' 'offer7' 'offer8' 'offer9' 'offer10');
   array orig[10] _temporary_;
   call missing(of orig[*]);
   do i = 1 to dim(o);
      if o[i] > cur_offer then orig[i] = o[i];
   end;
   call sortn(of orig[*]);

   _offer1 = .; _offer2 = .;
   Best_offer1='No_offer';  Best_offer2='No_offer';
   do i = 1 to dim(o);
      if missing(orig[i]) then continue;
      _offer1 = whichN(orig[i], of o[*]);
      j = i+1;
      if j <= dim(o) then _offer2 = whichN(orig[j], of o[*]);
      do while( j <= dim(o) and _offer1 = _offer2);
         _offer2 = whichN(orig[j], of o[*]);
         j+1;
      end;
      leave;
   end;
   if _offer1 then Best_offer1 = label(_offer1);
   if _offer2 then Best_offer2 = label(_offer2);
drop i j;
run;

Also, Ksharp responded using Smallest() function. Choose whatever you like.

[novinosrin]

Hello @jamerse   Assuming I understand your requirement

data have;
input cur_offer offer1 - offer10;
datalines;
3	.	.	.	.	.	6	7	8	9	10
4	1	2	.	.	5	6	7	8	9	10
2	.	.	3	4	5	6	7	8	9	10
4	.	.	.	4	5	6	7	8	9	10
1	1	2	3	4	5	6	7	8	9	10
5	.	.	.	.	.	.	.	.	.	.
5	1	2	.	.	.	.	7	8	9	10
4	.	.	.	.	.	.	.	.	.	10
4	.	.	.	.	.	.	.	.	.	.
9	.	.	.	3	.	6	.	.	.	10
;
run;

data want;
set have;
array t offer1-offer10;
do _i=cur_offer+1 to dim(t);
 if t(_i)>. then do; 
 _j=sum(_j,1);
 if _j=1 then Best_offer1=vname(t(_i));
 else if _j=2 then Best_offer2=vname(t(_i));
 end;
 if _j=2 then leave;
end;
if cmiss(of Best_offer:)=2 then do;
Best_offer1='No Offer';
Best_offer2='No Offer';
end;
drop _:;
run;

[KachiM]

@Ksharp , @novinosrin 

Your solutions are very good. However, you have assumed sorted Array. The OP has clarified for values can be unsorted like in third row. 

Here is my revised solution:

data want;
   set have;
   array o offer1 - offer10;

   array orig[10] _temporary_;
   call missing(of orig[*]);
   do i = 1 to dim(o);
      if o[i] > cur_offer then orig[i] = o[i];
   end;
   call sortn(of orig[*]);

   _offer1 = .; _offer2 = .;
   Best_offer1='No_offer';  Best_offer2='No_offer';
   do i = 1 to dim(o);
      if missing(orig[i]) then continue;
      _offer1 = whichN(orig[i], of o[*]);
      j = i+1;
      if j <= dim(o) then _offer2 = whichN(orig[j], of o[*]);
      do while( j <= dim(o) and _offer1 = _offer2);
         _offer2 = whichN(orig[j], of o[*]);
         j+1;
      end;
      leave;
   end;
   if _offer1 then Best_offer1 = vname(o[_offer1]);
   if _offer2 then Best_offer2 = vname(o[_offer2]);
drop i j _:;
run;

[Ksharp]

datasp,

No. My code does not ' assumed sorted Array ' .

🙂

[KachiM]

@Ksharp 

Your solution doesn't require sorted array. My observation on your solution which I missed to say was that the OP's requirement was not met for the 12-th row. OP wanted 'offer6' and 'offer7'.

Best regards,

DataSP

[jamerse]

@KachiM @novinosrin @Ksharp  Thank you very much for your guys support. They are all awesome !!!!. It is more complicated than I thought. Thank you all again :).

[KachiM]

@jamerse 

Thanks for accepting my solution. A revised version was placed as part of my observation of @Ksharp and @novinosrin .