BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jamerse
Fluorite | Level 6

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.

 

Offer.png

 

Thank you very much for all your contribution and support.

 

Best Regards,

Jame.

1 ACCEPTED SOLUTION

Accepted Solutions
KachiM
Rhodochrosite | Level 12

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

View solution in original post

11 REPLIES 11
KachiM
Rhodochrosite | Level 12
 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
Fluorite | Level 6

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

 

Thank you.

KachiM
Rhodochrosite | Level 12

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
Super User
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
Rhodochrosite | Level 12

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
Tourmaline | Level 20

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
Rhodochrosite | Level 12

@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
Super User

datasp,

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

🙂

 

KachiM
Rhodochrosite | Level 12

@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
Fluorite | Level 6

@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
Rhodochrosite | Level 12

@jamerse 

 

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 1740 views
  • 6 likes
  • 4 in conversation