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.
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
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?
Thank you datasp for looking it up. My expected result for Best_Offer1 is 6 and Best_Offer2 is 7.
Thank you.
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
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;
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.
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;
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;
datasp,
No. My code does not ' assumed sorted Array ' .
🙂
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
@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 :).
Thanks for accepting my solution. A revised version was placed as part of my observation of @Ksharp and @novinosrin .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.