DATA Step, Macro, Functions and more

Find out the nearest branch for the customers

Reply
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.

NameAddresscityStateBranch1_distanceBranch2_distanceBranch3_distanceBranch4_distanceBranch5_distanceBranch6_distanceBranch7_distance
James892 TRUMAN CIRFRISCOTX12.5645.8954.2378.978.723.8714.27

With this data, I've found the least 3 distance via smallest function.

NameAddresscityStateBranch1_distanceBranch2_distanceBranch3_distanceBranch4_distanceBranch5_distanceBranch6_distanceBranch7_distancemin-distance1min-distance2min-distance3
James892 TRUMAN CIRFRISCOTX12.5645.8954.2378.978.723.8714.2712.5614.2723.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.

NameAddresscityStateBranch1_distanceBranch2_distanceBranch3_distanceBranch4_distanceBranch5_distanceBranch6_distanceBranch7_distancemin-distance1min-distance2min-distance3Branch1_distanceBranch7_distanceBranch6_distance
James892 TRUMAN CIRFRISCOTX12.5645.8954.2378.978.723.8714.2712.5614.2723.8712.5614.2723.87
Super Contributor
Posts: 298

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
Super User
Posts: 7,942

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.

Respected Advisor
Posts: 4,173

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.

    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;

PROC Star
Posts: 7,467

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

Ask a Question
Discussion stats
  • 4 replies
  • 266 views
  • 0 likes
  • 5 in conversation