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.

SAS and VBA (4) : fill missing values with last valid observation

In many data management routines, it is common to fill the missing values with the last valid one. For example, we want to maintain the patient visit log about several patients, which records their weight for each visit. Given these patients’ absence for the appointments, the data analyst has to fill the the empty weight value with the last valid observation. This log includes three columns: patient ID, visit ID and weight.

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 is quite flexible at those occasions. If the cell has missing value, we can assign a R1C1 formula to the cell to obtain non-missing value directly from its top neighboring cell. As the result, the logic is a simple one-sentence clause.

Sub Locf()
'  If a cell in the 3rd column is blank then fill with the previous non-missing value
   Range("C1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
'  Format patient ID with 000
   Columns("A").NumberFormat = "000"
End Sub

SAS

In SAS, we need to set up a temporary variable in a DATA STEP to memorize the valid value by the RETAIN statement. Then a conditional structure is used to exchange the values between the weight variable and the temporary variable.

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;  

data result;
   set patient;
*  Format patient ID with 000;
   format ptno z3. ; 
   retain tempvar 0;
   if missing(weight) = 1 then weight = tempvar;
   else tempvar = weight;
   drop tempvar;
run;

Conclusion
SAS is a procedural language, while VBA enjoy its power based on its many objects and properties. However, one common thing in writing good codes for both of them is to avoid the unnecessary explicit loops.

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.

SAS and VBA (2) : cross tabulation and bar chart

No other tools can challenge Excel’s stance in the data analysis world. I didn’t spot many computers that are not installed with it, and I assume that everybody who faces a computer during work has to use it sometime. With the power of VBA, it is all programmable and could realize very complicated purposes without any mouse-clicking. While it is very popular to compare SAS and R, I feel that it is also meaningful to compare SAS and VBA, since these two are both well supported proprietary softwares from the great companies.

Here the example is about cross tabulation and the following visualization with a stacked bar chart. Let’s borrow the small data set SASHELP.CLASS from SAS, which includes 19 teenagers. We are interested see the total height broken down by age and sex.

Name	Sex	Age	Height	Weight
Alfred	M	14	69	112.5
Alice	F	13	56.5	84
Barbara	F	13	65.3	98
Carol	F	14	62.8	102.5
Henry	M	14	63.5	102.5
James	M	12	57.3	83
Jane	F	12	59.8	84.5
Janet	F	15	62.5	112.5
Jeffrey	M	13	62.5	84
John	M	12	59	99.5
Joyce	F	11	51.3	50.5
Judy	F	14	64.3	90
Louise	F	12	56.3	77
Mary	F	15	66.5	112
Philip	M	16	72	150
Robert	M	12	64.8	128
Ronald	M	15	67	133
Thomas	M	11	57.5	85
William	M	15	66.5	112

VBA
                                      

Pivot table has many wonderful features. It can easily aggregate data like OLAP with multiple dimensions, which makes it the most suitable tool for making cross tabs. Also because the pivot table define the fields, making a following pivot chart by codes is much more easier than any manual work.

Sub CreatePvt()
'   Set storage path for the pivot table
  Dim myPTCache As PivotCache, myPT As PivotTable
  Dim myPC As Chart

'   Delete the sheet containing the previous pivot table
  Application.ScreenUpdating = False
  On Error Resume Next
  Application.DisplayAlerts = False
  Sheets("Pivot table").Delete
  On Error GoTo 0
  
'   Create the cache
  Set myPTCache = ActiveWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
  
'   Add a new sheet for the pivot table
  Worksheets.Add
  ActiveSheet.Name = "Pivot table"
  
'   Create the pivot table
  Set myPT = ActiveSheet.PivotTables.Add( _
      PivotCache:=myPTCache, TableDestination:=Range("A1"))
'  Format the pivot table
   With myPT
       .AddFields RowFields:="Sex", _
           ColumnFields:="Age"
       With .PivotFields("Height")
           .Orientation = xlDataField
           ' Type of pivot table functions at http://goo.gl/F9rJh
           .Function = xlSum
           .Position = 1
        End With
       .NullString = "0"
       .DisplayFieldCaptions = False
       .TableStyle2 = "PivotStyleMedium14"
   End With
   
' Add the pivot chart
   Set ChartDataRange = myPT.TableRange1.Offset(1, 0).Resize(myPT.TableRange1.Rows.Count - 1)
   ActiveSheet.Shapes.AddChart.Select
   Set myPC = ActiveChart
' Format the pivot chart
   With myPC
       .SetSourceData Source:=ChartDataRange
      .ChartType = xlColumnStacked
      .SetElement (msoElementChartTitleAboveChart)
      .ChartTitle.Caption = " "
      .ChartStyle = 16
   End With
End Sub

SAS

In SAS, PROC REPORT is a better procedure than its older predecessors like PROC FREQ and PROC TABULATE. Similarly, the SG procedures are significantly more flexible than PROC GPLOT.

* Clear the old html outputs;
ods html close; 
ods html;

* Create the cross tabulation;
options missing = 0;
proc report data = sashelp.class nowd;
   columns sex age,height n;
   define sex / group ' ';
   define age / across ' ';
   define height / sum ' ';
   define n / 'Grand Total';
   rbreak after / summarize ;
run;
 
* Creat the statistical graph;
proc sgplot data = sashelp.class;
   vbar sex / response = height group = age;
   yaxis grid;
run;

Conclusions

In this demo, SAS would allow fewer lines of codes. Excel/VBA can do the same job and is available everywhere. And they are both highly customizable, and bring a lot fun in creating a table or a chart.

SAS and VBA (1): Conditional formatting

“Traffic lighting” applies distinctive colors to any numeric variables to indicate the ranges, which is particularly important for Excel reporting. In SAS, it can be easily realized by a user defined format. For example, if I want to add yellow color attribute to all numeric variables, which are great than 60, I can create a color format and cast it toward the target Excel file created by ODS destination.

data class;
   set sashelp.class;
run;

* Create a user defined format
proc format; 
   value range 
      60 - high = 'yellow'
      other = 'white'; 
run;

* Apply the color format for all numeric variables
ods html file = "c:\tmp\label_excel.xls" style = minimal;
proc print data = class nobs;
   var _character_ ;
   var _numeric_ / style = [background = range.];
run;
ods html close;

Similarly, a VBA subroutine can do the global search based on the specified ranges with a looping structure around the numeric variables. The looking of the resulting Excel files by either SAS or VBA are essentially identical.

Sub global_label()
    Dim Cell As Object
    Dim myCell As Range
    Dim myRange As Range
   
'   Specify selection ranges
    If TypeName(Selection) <> "Range" Then Exit Sub
    If Selection.CountLarge = 1 Then
       Set myRange = ActiveSheet.UsedRange
    Else
       Set myRange = Application.Intersect(Selection, ActiveSheet.UsedRange)
    End If
    
'   Only search numeric cells 
    On Error Resume Next
    Set myRange = myRange.SpecialCells(xlConstants, xlNumbers)
    If myRange Is Nothing Then Exit Sub
    On Error GoTo 0
    
'   Aggregate cells
    For Each Cell In myRange
        If Cell.Value > 60 Then
            If myCell Is Nothing Then
                Set myCell = Cell
            Else
                Set myCell = Application.Union(myCell, Cell)
            End If
        End If
    Next Cell

'   Label qualified cells
    If myCell Is Nothing Then
        MsgBox "No matching cell is found"
    Else
        myCell.Select
        With Selection.Interior
            .Pattern = xlSolid
            .Color = 65535
        End With
    End If
End Sub

Transform a SAS data set to an Excel pivot table by VBA

Pivot Table in Excel is the popular data report format, which is similar to an OLAP cube that aggregates data at any dimensions. To create a pivot table for a table with lots of columns, it usually takes 100+ drags and clicks to get job done, which is somehow annoying.

I didn't try the SAS’s Add-in for Microsoft Office or SAS Enterprise Guide yet. However, an easy solution to transform a SAS data set toward an Excel pivot table is possibly to use some VBA scripts under Excel. For example, SASHELP.PRDSAL2, which is a free data set shipped with SAS, records the furniture sales in 64 states of the three countries from 1995 to 1998, and has total 23,040 observations and 11 variables. This data set can be transformed to an Excel pivot table very quickly by two simple steps.

Step 1

In SAS, a data set can be exported toward an XLS file through ODS destination. Although it is still based on HTLM format, it can be opened by Excel.

ods html file = 'c:\tmp\prdsal2.xls' style = minimal;
title;
proc print data = sashelp.prdsal2 noobs;
run;
ods html close;

Step 2

Next step we click on this file, press ALT + F11 to enter VBA editor, paste the VBA code below and run it. Then the pivot table is created. The good thing about this method is that the pivot table can be replicated anywhere by such a VBA subroutine, and it is customizable for particular needs. The example was finished in Excel 2007.

Sub createPT()
'   Set storage path for the pivot table 
    myDataset = "sashelp.prdsal2"
    myFilepath = "c:\tmp\" & myDataset & "_" & Format(Date, "dd-mm-yyyy") & ".xlsx"
    Dim myPTCache As PivotCache
    Dim myPT As PivotTable

'   Delete the sheet containing the previous pivot table   
    Application.ScreenUpdating = False
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Pivot_Table_Sheet").Delete
    On Error GoTo 0
    
'   Create the cache
    Set myPTCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
    
'   Add a new sheet for the pivot table
    Worksheets.Add
    ActiveSheet.Name = "Pivot_Table_Sheet"
    
'   Create the pivot table
    Set myPT = ActiveSheet.PivotTables.Add( _
        PivotCache:=myPTCache, TableDestination:=Range("A5"))
    With myPT
        .PivotFields("COUNTRY").Orientation = xlPageField
        .PivotFields("STATE").Orientation = xlRowField
        .PivotFields("PRODTYPE").Orientation = xlRowField
        .PivotFields("PRODUCT").Orientation = xlRowField
        .PivotFields("YEAR").Orientation = xlColumnField
        .PivotFields("QUARTER").Orientation = xlColumnField
        .PivotFields("MONTH").Orientation = xlColumnField
        .PivotFields("ACTUAL").Orientation = xlDataField
        .PivotFields("PREDICT").Orientation = xlDataField
        .DataPivotField.Orientation = xlRowField
        '  Add a calculated field to compare the predicted value and the actual value
        .CalculatedFields.Add "DIFF", "=PREDICT-ACTUAL"
        .PivotFields("DIFF").Orientation = xlDataField
        ' Specify a number format
        .DataBodyRange.NumberFormat = "$#, ##0.00"
        ' Apply a style for pivot table
        .TableStyle2 = "PivotStyleLight18"
    End With
    Range("A1").FormulaR1C1 = "Pivot table made from data set" & " " & myDataset
    Range("A2").FormulaR1C1 = "Prepared by WWW.SASANALYSIS.COM on " & Date
    ActiveWorkbook.SaveAs Filename:=myFilepath, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

An easy solution for Multi-Sheet EXCEL reporting

Currently the only way to output SAS datasets as a multi-sheet EXCEL workbook for reporting is to use ExcelXP ODS tagset. I like this method a lot, because it can generate stylish multiple EXCEL sheets and is highly customizable. However, in practice it has some weaknesses. 1 - Running this tagset is resource-costly, since it depends on an 8k lines SAS codes - ExcelXP.sas. While dealing with a large SAS dataset, it always gets jammed. 2- It only allows one grouping variable by the BY statement inside the output procedures (PROC REPORT, PROC PRINT, etc.). 3 - The user often has to estimate the width for each column in EXCEL.

Actually we can use SAS macro and VBA macro together to obtain high-quality multi-sheet EXCEL workbook. The workflow is pretty simple: first a SAS macro splits a SAS dataset into many XLS files in a folder through ODS HTML targset. Second a VBA macro merges those single XLS files as sheets in to a workbook. For example, SAS shipped with a sample dataset SASHELP.PRDSAL2 with 23040 observations and 11 variables. If we want to generate a multi-sheet EXCEL workbook grouped by two variables such as ‘state’ and ‘year’, we can set up an empty directory in the hard disk and run a macro like below. As a result, we will have a number of small XLS files.

%macro split(data = , folder = , clsvar1 = , clsvar2 = );
   options nocenter nodate nonumber ps = 9000; 
   title; footnote;
   ods listing close;
   proc sql noprint;
      create table _tmp01 as 
      select &clsvar1, &clsvar2, count(*) as number
      from &data
      group by &clsvar1, &clsvar2
      order by &clsvar1, &clsvar2
   ;quit;
   data _tmp02;
      set _tmp01 nobs = nobs;
      where number gt 0;
      index = _n_;
      call symput('nobs', nobs);
   run;
   %do i = 1 %to &nobs;
      proc sql noprint;
         select &clsvar1, &clsvar2
            into:clsvar1name,:clsvar2name
         from _tmp02
         where index = &i
      ;quit;
      %let filepath = &folder\%sysfunc(dequote(&clsvar1name))_%sysfunc(dequote(&clsvar2name)).xls;
      ods html file = "&filepath " style = minimal; 
      proc print data = &data noobs label;
         where &clsvar1 = "&clsvar1name" and &clsvar2 = &clsvar2name;
      run;
   %end;
   ods listing; 
   ods html close;
%mend;
%split(data = sashelp.PRDSAL2, folder = C:\test1, clsvar1 = state , clsvar2 = year)

Then we can open EXCEL, press ALT+F11, paste the VBA code below and run it. Then we will be able to have a decent multi-sheet EXCEL workbook. The biggest strength for this method is that it is very fast – the overall process (running SAS macro and VBA macro) only takes less than a minute for this relatively large dataset SASHELP.PRDSAL2. And it can be expanded to many grouping variables by modifying the SAS macro a little. In conclusion, for big data EXCEL reporting, combining SAS macro and VBA macro together is a good alternative other than ExcelXP ODS tagset.
VBA Draft 3.1 Blogpost

A scorecard for probability of default with sparkline

In the 1st chapter of their must-read credit risk modeling book, Gunter and Peter used the ratios of working capital(WC), retained earnings(RE), earnings before interest and taxes(EBIT), sales(S) and market value of equity(ME) over either total liabilities (TL) or total assets(TA), to build a logit default risk model for 4000 records by 791 firms through 10 years [Ref.1]. The authors implemented VBA’s user-defined functions in Excel to realize the modeling and scoring procedures. In SAS, Proc Logistic does the same job.

Macros in SAS and Excel can be used together. Excel2010’ new Sparkline functions visualize the fluctuations of values in many rows. For those firms which eventually didn’t default in Gunter and Peter’s example, Sparkline can help observe the changing pattern of default risk for specific firms, such as which year the firms’ maximum default risk occur. Collin and Eli disclosed some definitions to translate a SAS dataset to an Excel table [Ref. 2]. Thus, by using their method, a SAS macro that generates VBA code will automate this process for a default risk scorecard with Sparkline.

References:
1. Gunter Löeffler and Peter Posch. ‘Credit Risk Modeling using Excel and VBA’. The 2nd edition. Wiley.
2. Collin Elliot and Eli Morris. ‘Excel lent SAS Formulas: The Creation and Export of Excel Formulas Using SAS’ . SAS Global 2009.

/*******************READ ME*********************************************
* - A scorecard for probability of default(PD) with sparkline -
*
* SAS VERSION:    9.1.3
* EXCEL VERSION:  2010
* DATE:           31may2011
* AUTHOR:         hchao8@gmail.com
*
****************END OF READ ME******************************************/

%macro splscd(data = , path = , filename = );
   /*****************************************************************
   *  MACRO:      splscd()
   *  GOAL:       create xls and vba for a scorecard with sparkline
   *  PARAMETERS: data     = dataset for modeling and scoring
   *              path     = output path
   *              filename = name for scorecard
   *****************************************************************/
   options mprint mlogic;
   data _excelCol(where = (start le 256));  
      length label $2;  
      do c1 = -1 to 25;  
         do c2 = 0 to 25;  
            alpha1 = byte(rank('A')+ c1);  
            alpha2 = byte(rank('A')+ c2);  
            label = compress(alpha1||alpha2, " @");  
            start + 1;  
            fmtName = "column";  
            output;  
         end;  
      end;  
   run;

   proc format cntlin = _excelCol; 
   run; 

   proc logistic data = &data;
      model default = WCoverTA REoverTA EBIToverTA MEoverTL SoverTA;
      score data = &data out = _scored;
   run;

   data _tmp01;
      set _scored;
      where default = 0;
      keep id year p_1;
   run;

   proc sort data = _tmp01;
      by id year;
   run;

   proc transpose data = _tmp01 out = _tmp02(keep = id year:) prefix = year;
      by id;
      id year;
      var p_1;
   run;

   ods listing close;
   ods output variables = _vartab;
   proc contents data = _tmp02; 
   run;

   proc sql;
      select variable into: varlist separated by ' '
      from _vartab
      order by substr(variable, 5, 4)
      ;
      select count(*) format = column. into: col_num
      from _vartab
      ;
      select count(*)+1 format = column. into: spl_col
      from _vartab
      ;
   quit;

   data _tmp03;
      retain &varlist;
      set _tmp02 nobs = nobs;
      sparkline =.;
      call symput('nobs_plus', nobs + 1);
   run;

   ods html file = "&path\&filename..xls" style = minimal;
   option missing = ''; 
   title; footnote;
   proc print data = _tmp03 noobs;
   run;
   ods html close;

   proc sql;
      create table _tmp04 (string char(200));
      insert into _tmp04
      values("Sub sas2vba()")
      values("''''''''CREATE SPARKLINE'''''''''")
      values('Columns("spl_col:spl_col").ColumnWidth=30')
      values("Dim mySG As SparklineGroup")
      values('Set mySG = _ ')
      values('Range("$spl_col$2:$spl_col$nobs_plus").SparklineGroups.Add(Type:=xlSparkColumn, SourceData:="B2:col_numnobs_plus")')
      values('mySG.SeriesColor.ThemeColor=6')
      values("mySG.Points.Highpoint.Visible=True")
      values("''''''''FORMAT THE TABLE'''''''''")
      values('ActiveSheet.ListObjects.Add(xlSrcRange,Range("$A$1:$spl_col$nobs_plus"), , xlYes).Name="myTab"')
      values('Range("myTab[#All]").Select')
      values('ActiveSheet.ListObjects("myTab").TableStyle="TableStyleMedium1"')
      values("''''''''SAVE AS EXCEL2010 FORMAT'''''''''")
      values('ChDir "sas_path"')
      values('ActiveWorkbook.SaveAs Filename:="sas_path\excel_file.xlsx",FileFormat:=xlOpenXMLWorkbook,CreateBackup:=False')
      values("End Sub")
      ;
   quit;

   data _tmp05;
      set _tmp04;
      string = tranwrd(string, "spl_col", "&spl_col");
      string = tranwrd(string, "nobs_plus", "&nobs_plus");
      string = tranwrd(string, "col_num", "&col_num");
      string = tranwrd(string, "sas_path", "&path");
      string = tranwrd(string, "excel_file", "&filename");
      if _n_ in (3, 6, 10) then string = compress(string, ' ');
   run;

   data _null_;
      set _tmp05;
      file "&path\&filename..bas";
      put string;
   run;

   proc datasets nolist;
      delete _:;
   quit;
%mend splscd;

%splscd(data = test1, path = c:\tmp, filename = scorecard);

SAS makes spreadsheet for reporting

Excel is the last stop in the pipeline of my daily work. Most clients of mine like colorful multi-sheet spreadsheets more than plain CSV files (I guess they all use Windows). I am not a power user of Excel, and honestly I am a little afraid of it: sometimes it got wrong while I accidently dragged the cells to make duplicates. As the result, I tend to have everything ready in SAS and treat Excel as the close box. At the beginning I preferred to use the high-tech ODBC engine to exchange data; eventually I gave up when I found that my IT support doesn’t know how to add SAS/ACCESS module though we have the license. The EXPORT procedure is widely used, but it does not allow traffic lighting, which labels value with distinctive colors. MSOFFICE2K, an ODS tagset from SAS to Excel, ceased to work [Ref. 1]. Besides those methods, SAS has some alternative ways to prepare pretty Excel reports. Each year Vincent in SAS updates ExeclXP, a 6k-line SAS code, for multi-sheeting output by ODS tagset [Ref. 2]. This year in his paper, Romain elaborated possibly all the 8 methods, and no wonder his amazing summarization won the best paper award in the code’s corner section [Ref. 3].

Three routines in SAS satisfy 99% of my requirements for an Excel report. First, SAS‘ HTML ODS tagset can directly write XLS file. It is good enough for any single-sheet spreadsheet. Second, thanks to Vincent, ExcelXP provides the perfect tool to generate multi-sheet spreadsheets. The only place where I need to do extensive coding is to find optimal values for the ‘absolute_column_width’ setting in Excel. Hope next year the code would be improved to address this particular question. Third, if part of the cells in Excel needs calculation, I would like to call DDE, a old but still robust technology, to read data into SAS and later write back (I also use R as a scientific calculator to do algebra).

In conclusion, the report by codes can be reproduced more easily than that by many point-and-click operations. That’s probably why I like SAS more than Excel.

Reference:
1. ‘Vanilla output using ODS’. SAS-L. 05May2011
2. Vincent DelGobbo. ‘Creating Stylish Multi-Sheet Microsoft Excel Workbooks the Easy Way with SAS’. SAS Global 2011.
3. Romain Miralles. ‘Creating an Excel report: A comparison of the different techniques’. SAS Global 2011. http://support.sas.com/rnd/papers/index.html

/*******************READ ME*********************************************
* -  SAS MAKES SPREADSHEET FOR REPORTING -
*
* SAS VERSION:  SAS 9.1.3
* DATE:         11may2011
* AUTHOR:       hchao8@gmail.com
*
****************END OF READ ME******************************************/

****************(1) MULTIPLE SHEET REPORTING FOR EXCEL *****************;
******(1.1) FIND EXCEL'S COLUMN WIDTH AND MAKE TRAFFIC LIGHTS***********;
%macro excelcol(data = , groupvar = );
   /*****************************************************************
   *  MACRO:      excelcol()
   *  GOAL:       find the optimum column size for EXCEL  
   *  PARAMETERS: data       =  data used for output
   *              groupvar   =  variable for sheet separation
   *****************************************************************/
   %global var_list len_list;
   ods listing close;
   ods output variables = _varlist;
   proc contents data = &data; 
   run;
   proc sort data = _varlist;
      by format num;
   run;
   data _varlist;
      set _varlist;
      len_var = length(variable);
      len_lab = length(label);
      max_len = max(of len:);
   run;
   
   proc sql;
      select variable into: var_list separated by ' '
      from _varlist
      where lowcase(variable) ne "&groupvar"
      ;
      select max_len into: len_list separated by ','
      from _varlist
      where lowcase(variable) ne "&groupvar"
      ;
   quit;
   ods listing;
%mend excelcol;
%excelcol(data = sashelp.cars, groupvar = origin);

proc format;
   value price  
      40000 - high = '#FFFFCC'
      26000 -< 40000 = 'Yellow'
      other = '#FF9900';
run;
ods listing;

******(1.2) WRITE FINAL REPORT TO EXCEL********************************;
proc template;
   define style styles.xlsansprinter;
      parent = styles.sansprinter;
      style header from header /
         font_size = 10pt just = center vjust = bottom;
   end;
run; quit;

ods tagsets.excelxp path="c:\" file="cars.xml" style=xlsansprinter
               options(sheet_interval="bygroup" sheet_label=" "
                            suppress_bylines="yes" autofilter="2-14"
                            absolute_column_width="&len_list");
title; footnote;
proc report data = sashelp.cars nowd split='*';
   by make;
   column &var_list;
   define model / style(column) = [just=center font_weight=bold];
   define invoice / style(column) = [foreground=lime];
   compute model;
      rownum + 1;
      if (mod(rownum, 2) ne 0)
      then call define(_row_, 'style', 'style = [background=#99ccff]');
   endcomp;
   compute msrp;
      call define(_col_, 'style', 'style = [background=price.]'); 
   endcomp;
   compute before _page_;
      line "Made by &sysuserid on &sysday., &sysdate";
   endcomp;
run; quit;
ods tagsets.excelxp close;
ods listing;

****************(2) SINGLE SHEET REPORTING FOR EXCEL *****************;
ods listing close;
ods html file = "c:\cars2.xls" gpath = "c:\" style = minimal;
title; footnote;
proc print data = sashelp.cars; 
   id make; 
   var &var_list;
   var msrp / style = [background=price.]; 
run;

proc gplot data = sashelp.cars;
   plot msrp * invoice;
run;
ods html close;
ods listing;

****************(3) DDE BETWEEN SAS AND EXCEL ************************;
******(3.1) EXTRACT DATA FROM EXCEL TO COMPUTE IN SAS*****************;
filename _infile dde 'clipboard';
data _tmp01;
   infile _infile notab missover dlm = '09'x dsd;
   informat var1 var2 dollar8.;
   input var1 var2;
run;

data _tmp02;
   set _tmp01;
   mean = mean(of var:);
   std = std(of var:);
run;

******(3.2) WRITE STATISTICS BACK TO EXCEL****************************;
options noxwait noxsync;
x "c:\cars2.xls";

filename _outfile dde 'excel|c:\[cars2.xls]cars2!r2c17:r28c18';
data _null_; 
   set _tmp02;
   file _outfile notab dlm='09'x; 
   format mean std dollar8.; 
   put mean std; 
run; 

****************END OF ALL CODING***************************************;

Proc Fcmp(1): from VBA to SAS

Why use SAS in finance: SAS is a distinguished software package in statistics with more than 40-year development history. Starting from as a scripting tool to do ANOVA for agricultural experimental design in North Carolina, SAS has been heavily built on generalized linear model. For example, SAS institute consistently improve linear model procedures, from Proc Anova, Proc Glm, Proc Mixed to the latest Proc Glimmix. In a summary, SAS is pretty good at processing and analyzing any linear or non-linear models. However, the foundation for finance model, such as fixed income products and derivatives, is continuous-time equations, such as Black-Scholes formula. Most likely, quantitative analysts tend to price the products by solving those equations. So, in a word, the finance analyst is always working with equations, or many equations. Obviously here SAS is not good at it. Yes, SAS has more than 900 functions. And they are still not enough to keep up with the fast-pace of Wall Street. That is why the quants use Matlab, C++ and Excel VBA, instead of SAS. Then how the quants need to create their own equations in SAS? And how they build their function library or include the 3rd party library? Proc Fcmp may be the rescue.
Why Proc Fcmp? Finally we have Proc Fcmp, an equation editor. Proc Fcmp is a formidable tool for building function and even function library. All self-built or third party functions are stored in customer-specified package for future usage. Like Excel VBA, Proc Fcmp can construct equivalent subroutine and function. The nice thing is that all the function-based variables are encapsulated without any explicit declaration ( I hate nested macros: the variables would surf around from here to there). In addition, SAS Function Editor is an excellent tool viewer to manage and check all functions.
Conclusion: Look at the codes below, you see that Excel VBA and SAS Proc Fcmp are quite similar. A VBA developer can switch to SAS developer very smoothly in a short period. Also many people can work with a function package simultaneously through a distant SAS server, while each of them builds individual function. The quants may feel more comfortable to use SAS than VBA. Another good thing is that, by using Proc Proto, C++ function can be introduced into Proc Fcmp. That means that even C++ developer can also explore the turf of SAS language. Given that SAS is also a wonderful database management software, I expect that more and more people would embrace SAS through Proc Fcmp in the finance area.

Reference: Jørgen Boysen Hansen. Using the new features of Proc Fcmp in risk management at dong energy A/S. DONG Energy A/S.

'USE EXCEL VBA TO TO GRADE THE SCORES OF 28 STUDENTS
Function Grade(score)
If IsGrade(score) Then
Select Case score
Case Is <= 60  Grade = "F"             
Case 60.5 To 70   Grade = "D"            
Case 70.5 To 80   Grade = "C"            
Case 80.5 To 90   Grade = "B"             
Case IS > 90      Grade = "A"
End Select
Else
Grade = " "
End If
End Function

/*USE PROC FCMP TO GENERATE THE FUNCTION */
proc fcmp outlib=sasuser.myfunction.grade;
function grade(score);
select;
when (Score GE 90) return ("A");
when (Score GE 80) return ("B");
when(Score GE 70) return ("C");
when (Score GE 60) return ("D");
when (Score NE .)   return ("F");
otherwise;
end;
endsub;
run;
quit;
/*APPLY THE FUNCTION TO GRADE THE SCORES OF 28 STUDENTS*/
options cmplib=sasuser.myfunction;
data exam_one_graded;
set exam_one;
Grade_one=grade(score);
run;