Friday, September 28, 2012

SAS and VBA (3) : lower triangle multiplication table


Flow control and looping is a very important aspect for any programming language. To see how to index a particular value in the languages’ default data type, creating a lower triangle multiplication table looks like a good test, since it is a simple question but still requires the skills to implement a nested loop and a condition statement.

VBA 

Excel has row number (1, 2, etc.) and column number (A, B, etc.) for each cell. Then in VBA, we can use Range() or Cells() to select those cells in any particular worksheet. So it will be very easy to implement the logic in VBA to create a lower triangle multiplication table.

Sub Mt()
   ActiveSheet.Range("A:Z").EntireColumn.Clear
   For i = 1 To 9
      For j = 1 To 9
         If i >= j Then
            Cells(i, j) = i*j
         End If
      Next j
   Next i
   MsgBox "Done"
End Sub

SAS 

SAS’s data set doesn’t have exact indexes for row or column. There is internal automatic variables, which is _N_, for rows. However, to specify the columns, we have to declare a temporary array. And in this demo, the position of the OUPUT statement has to be between the inner loop and the outer loop.

data mt;
   array a[9] col1-col9;   
   do i = 1 to 9;           
      do j= 1 to 9;   
         if i >= j then a[j] = i*j;   
      end;
      output;         
   end;
   drop i j;
   put "Done";
run;

Conclusion 
To select a few columns or variables, array is a must in SAS. That is possible why the DATA STEP array is so important in SAS. For beginners, VBA is an easier way to apply loops.

Good math, bad engineering

As a formal statistician and a current engineer, I feel that a successful engineering project may require both the mathematician’s abilit...