## Find out the nearest branch for the customers

Regular Contributor
Posts: 168

# Find out the nearest branch for the customers

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
Super Contributor
Posts: 320

## Re: Find out the nearest branch for the customers

[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]

Super User
Posts: 9,599

## Re: Find out the nearest branch for the customers

Alternatively, sort your array (for example: Bubble sort - Wikipedia, the free encyclopedia)

Then array{1-3} will contain your values.

Posts: 4,736

## Re: Find out the nearest branch for the customers

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.

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;

PROC Star
Posts: 8,163

## Re: Find out the nearest branch for the customers

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

Discussion stats
• 4 replies
• 282 views
• 0 likes
• 5 in conversation