Do loop and If statement in Proc IML

Question

I have table1 that contains one column (city), I have a second table (table2) that has two columns (city, distance),

I am trying to create a third table, table 3, this table contains two columns (city, distance), the city in table 3 will come from the city column in table1 and the distance will be the corresponding distance in table2.

I tried doing this using Proc IML based on Joe's suggestion and this is what I have.

proc iml;

use Table1;

read all var _CHAR_ into Var2 ;


use Table2;
read all var _NUM_ into Var4;
read all var _CHAR_ into Var5;


do i=1 to nrow(Var2);
   do  j=1 to nrow(Var5);
    if Var2[i,1] = Var5[j,1] then
    x[i] = Var4[i];
end;


create Table3 from x;
append from x;
close Table3 ; 
quit;

I am getting an error, matrix x has not been set to a value. Can somebody please help me here. Thanks in advance.

Answer 1

The technique you want to use is called the "unique-loc technique". It enables you to loop over unique values of a categorical variable (in this case, unique cities) and do something for each value (in this case, copy the distance into another array).

So that others can reprodce the idea, I've imbedded the data directly into the program:

T1_City = {"Gould","Boise City","Felt","Gould","Gould"};
T2_City = {"Gould","Boise City","Felt"};
T2_Dist = {10, 15, 12};

T1_Dist = j(nrow(T1_City),1,.);    /* allocate vector for results */
do i = 1 to nrow(T2_City);
   idx = loc(T1_City = T2_City[i]);
   if ncol(idx)>0 then 
      T1_Dist[idx] = T2_Dist[i];
end;

print T1_City T1_Dist;

The IF-THEN statement is to prevent in case there are cities in Table2 that are not in Table1. You can read about why it is important to use that IF-THEN statement. The IF-THEN statement is not needed if Table2 contains all unique elements of Table1 cities.

This technique is discussed and used extensively in my book Statistical Programming with SAS/IML Software.

Answer 2

You need a nested loop, or to use a function that finds a value in another matrix.

IE:

do i = 1 to nrow(table1);
  do j = 1 to nrow(table2);
...
  end;
end;