I stuck with the following scenario. Any help would be greatly appreciated.
I've the SAS dataset as below.
Name | Address | city | State | Branch1_distance | Branch2_distance | Branch3_distance | Branch4_distance | Branch5_distance | Branch6_distance | Branch7_distance |
James | 892 TRUMAN CIR | FRISCO | TX | 12.56 | 45.89 | 54.23 | 78.9 | 78.7 | 23.87 | 14.27 |
With this data, I've found the least 3 distance via smallest function.
Name | Address | city | State | Branch1_distance | Branch2_distance | Branch3_distance | Branch4_distance | Branch5_distance | Branch6_distance | Branch7_distance | min-distance1 | min-distance2 | min-distance3 |
James | 892 TRUMAN CIR | FRISCO | TX | 12.56 | 45.89 | 54.23 | 78.9 | 78.7 | 23.87 | 14.27 | 12.56 | 14.27 | 23.87 |
But I wish to produce the dataset which depicts the three nearest branch for the customer. Dataset like below or similar one. Here the complexity is to name the last three variables. There are also a situation where I've 25+ branches with 60K+ records as well.
Name | Address | city | State | Branch1_distance | Branch2_distance | Branch3_distance | Branch4_distance | Branch5_distance | Branch6_distance | Branch7_distance | min-distance1 | min-distance2 | min-distance3 | Branch1_distance | Branch7_distance | Branch6_distance |
James | 892 TRUMAN CIR | FRISCO | TX | 12.56 | 45.89 | 54.23 | 78.9 | 78.7 | 23.87 | 14.27 | 12.56 | 14.27 | 23.87 | 12.56 | 14.27 | 23.87 |
[1] Use SMALLEST() function to get the smallest by passing i = 1, 2, 3.
[2] Use WHICHN() function to the numeric order of variable for [1]
[3] Use VNAME() function to get the NAME for [2]
Alternatively, sort your array (for example: Bubble sort - Wikipedia, the free encyclopedia)
Then array{1-3} will contain your values.
I believe organizing your data in a long instead of a wide structure could make it easier for you. You then can use BY processing to determine and only select the top 3 ranks per category (business key). For most SAS Procedures it's beneficial to have data organized in a long structure.
DATA WORK.have;
INFILE DATALINES4
DLM=','
truncover
DSD;
INPUT
Name : $5.
Address : $20.
city : $6.
State : $3.
Branch1_distance : best32.
Branch2_distance : best32.
Branch3_distance : best32.
Branch4_distance : best32.
Branch5_distance : best32.
Branch6_distance : best32.
Branch7_distance : best32.
;
DATALINES4;
James,892 TRUMAN CIR,FRISCO,TX,12.56,45.89,45.89,78.9,78.7,23.87,14.27
;;;;
run;
data inter (drop=_: Branch: rename=(BName=Branch_Name BDist=Branch_Distance));
set have;
array bran {*} Branch:;
length BName $32. BDist 8.;
do _i= 1 to dim(bran);
if not missing(bran[_i]) then
do;
BName=vname(bran[_i]);
BDist=bran[_i];
output;
end;
end;
run;
proc sort data=inter out=want;
by Name Address City State Branch_Distance;
run;
data rank;
set want;
by Name Address City State Branch_Distance;
if first.State then Rank=0;
if first.Branch_Distance then Rank+1;
run;
Ram,
Some years ago Paul Dorfman posted a macro on the SAS-L bulletin board that may be exactly what you're looking for (combined with the other suggestions that have been provided). It allows you to sort by one variable, but have a second variable re-ordered according to the results of that sort.
Here is an example with your test data:
data have;
infile cards dlm='09'x;
informat name $20.;
informat address $50.;
informat city $30.;
informat state $2.;
input Name Address city State Branch1_distance Branch2_distance Branch3_distance Branch4_distance Branch5_distance Branch6_distance Branch7_distance;
cards;
James 892 TRUMAN CIR FRISCO TX 12.56 45.89 54.23 78.9 78.7 23.87 14.27
;
%macro combsort (arr1 =, arr2=, order= <);
drop __:;
do __g = hbound (&arr1) - 1 by 0 while (__s or __g > 1);
__g = int (__g / 1.3);
if __g in (0 ) then __g = 1;
else if __g in (9, 10) then __g = 11;
__s = 0;
do __j = lbound (&arr1) to hbound (&arr1) - __g;
__k = __j + __g;
if &arr1[__j] &order &arr1[__k] then continue;
__t = &arr1[__j];
&arr1[__j] = &arr1[__k];
&arr1[__k] = __t;
__tt = &arr2[__j];
&arr2[__j] = &arr2[__k];
&arr2[__k] = __tt;
__s = 1;
end;
end;
%mend;
data want;
set have;
array bdist(*) $15. branch:;
array bname(7) $10.;
do _n_=1 to 7;
bname(_n_)=scan(vname(bdist(_n_)),1,'_');
end;
%combsort(arr1=bdist, arr2=bname);
run;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: