SAS and VBA (8): What VBA can do for SAS?

The eventual answer is user interface (I really don't mean SAS/AF) with or without Excel.

In the Windows environment, SAS provides data access layer by ADO and ADO.NET. We can build the applications using SAS at the backend through ADO.NET. Since Visual Basic.NET has the similar syntax to VBA, if somebody is comfortable to code in Microsoft's Visual Studio, then developing a desktop app is always feasible although it requires quite a few efforts.

For example, let's run a simple linear regression using the variable of WEIGHT to predict HEIGHT based on the SASHELP.CLASS data set. We can use Excel's macro to fetch the output by SAS as a new result sheet in the same workbook. Similarly, a desktop application (.exe) will run SAS as batch mode without notice and return the regression result in a browser. The best part of developing such an application is that Excel or Visual Studio comes at nearly no cost. And as you know, the client doesn't need to know anything about SAS.

The UI in Excel

After hitting the button

The UI of a desktop app

After hitting the button

SAS and VBA (7): calculate running total

This is a simple routine for a lot of reporting work. Many people tend to do it in Excel by dragging the range after entering the formula in the 1st cell. However, coding in VBA and SAS will usually do it in more prompt and safe way.

VBA

VBA’s unique R1C1 formula is pretty handy once we get to know the rule. The 1st cell at the F column has different R1C1 formula than the cells below.

Sub Rt()
'   Find the last row
    FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("F1").Value = "Running total of Weight"
    Range("F2").FormulaR1C1 = "=RC[-1]"
    Range("F3:F" & FinalRow).FormulaR1C1 = "=RC[-1] + R[-1]C"
End Sub

SAS

It is incredibly easy to do the job in SAS. One line of code -- that is all! Obviously SAS beats VBA's three lines in this demo here.

data want;
   set sashelp.class;
   label total_weight = "Running total of Weight"; 
   total_weight + weight;
run;

SAS and VBA (6) : delete empty rows

One tricky question of the data work is to delete empty rows/observations in a raw file. A code snippet is particular useful to handle such cases. At the mean time, we need to know how many rows are actually removed as the execution result. The good news is that as programmable softwares, Excel/VBA and SAS are both good at dealing this job. In a simple demo text file below, there are actually two empty lines that have to be removed.

  
ptno visit weight
1 1 122  
1 2   
1 3   
1 4 123  
2 1 156  
2 3   

3 1 112  
3 2   

4 1 125 
4 2  
4 3 

VBA
VBA's CountA function is able to count the number of non-empty cells in any range of cells.Within a loop from the top to the bottom, it will help automatically remove those empty rows. In the codes, a message box is created to return the number of the rows deleted.

Sub DelEptRow()
    Dim myRow, Counter As Integer
    Application.ScreenUpdating = False
    For myRow = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(Rows(myRow)) = 0 Then
            Rows(myRow).Delete
            Counter = Counter + 1
        End If
    Next myRow
    Application.ScreenUpdating = True
'   Display the number of rows that were deleted
    If Counter > 0 Then
        MsgBox Counter & " empty rows were removed"
    Else
        MsgBox "There is no empty row"
    End If
End Sub

SAS
SAS can do the same thing with the combination of its MISSING function(decides if there is any missing value) and CATS function(concatenates all numerical and string variables and trims leading blanks). It is very convenient to apply the logic in a DATA STEP and let LOG tell how many lines are deleted.

data patient;  
   input @1 ptno @3 visit @5 weight;  
   infile datalines missover;   
   cards;  
1 1 122  
1 2   
1 3   
1 4 123  
2 1 156  
2 3   

3 1 112  
3 2   

4 1 125 
4 2  
4 3 
;;;  
run;

options missing = ' ';
data want;
   set patient nobs = inobs end = eof;
   if missing(cats(of _all_)) then delete;
*  Display the number of rows that were deleted;
   outobs + 1;
   counter = inobs - outobs;
   if eof then do;
      if counter > 0 then put counter "empty rows were removed";
      else put "There is no empty row";
   end;
   drop counter outobs;
run;

Conclusion
In a DATA STEP, SAS's implicit loop has two sides. The good side is that we do not need care about how to set up a loop most time which saves codes. The bad side is that sometimes it is hard to control which row we need to loop to.

SAS and VBA (5) : replace values quickly

SAS and VBA both have their unique and quick ways to replace values in one or multiple columns.

VBA
VBA has a wonderful function Replace for several columns or regions, where the changes are likely to be happened.

Sub Replace()
   With Columns("B")
      .Replace "F", "Female"
      .Replace "M", "Male"
   End With
End Sub

SAS
User-defined format by PROC FORMAT is the best way for quick replacements.

proc format;
   value $sex
      'F' = 'Female'
      'M' = 'Male'
   ;
run;

data want;
   set sashelp.class;
   format sex $sex.;
run;

Conclusion
For some data management operations such as string/number replacement, it is better way to use the languages' built-in features, instead of the loops and condition statements.