topic Re: Find out the nearest branch for the customers in SAS Programming

Find out the nearest branch for the customers

RamKumar — Thu, 06 Nov 2014 12:30:10 GMT

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

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

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

Re: Find out the nearest branch for the customers

KachiM — Thu, 06 Nov 2014 13:43:17 GMT

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

Re: Find out the nearest branch for the customers

RW9 — Thu, 06 Nov 2014 14:52:53 GMT

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

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

Re: Find out the nearest branch for the customers

Patrick — Thu, 06 Nov 2014 21:27:16 GMT

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;

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;

Re: Find out the nearest branch for the customers

art297 — Thu, 06 Nov 2014 23:00:32 GMT

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;

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