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

Use the set operator UNION in PROC SQL

SQL syntax contains a few set operators, such as UNION, EXCEPT and INTERSECT. The UNION operator concatenates the results of multiple SQL queries vertically into a single table for all matching rows, which I found particularly useful in PROC SQL while using SAS to manage data. Here come two examples.

Example 1 – Transpose data
PROC SQL can transform a dataset to any desired structure, without referring to DATA step or PROC TRANSPOSE. For example, SASHELP.CLASS can be transposed from wide to long by the UNION ALL clause, and reversely from long to wide by the MAX function and the GROUP clause.

From wide to long

data wide;
   set sashelp.class;
   drop sex;
run;

proc sql;
   create table long as
   select name, 'Age' as var, age as col1
   from wide
   union all
   select name, 'Weight' as var, weight as col1
   from wide
   union all
   select name, 'Height' as var, height as col1
   from wide
;quit;

From long to wide

proc sql;
   create table wide as
   select name,
      max(case when var = 'Age' then col1 end) as Age,
      max(case when var = 'Weight' then col1 end) as Weight,
      max(case when var = 'Height' then col1 end) as Height
   from long
   group by name;
quit;

Example 2 – Aggregate data into a cube
In SAS, PROC SQL doesn’t support the ROLLUP or CUBE clause. However, we can apply multiple UNION operators to simulate such functionality. For example, we can create a cube table to list all possible summations of the ACTUAL variable by STATE, PRODUCT, YEAR in the SASHELP.PRDSAL2 dataset. Afterward we can easily query this multi-dimensional data structure to look for interesting aggregation information, without running any other aggregating procedure again.

data prdsal2;
   set sashelp.prdsal2;
run;

proc sql;
   create table cube as
   select state,product, year, 'total by state, prodcut and year' as category,
   sum(actual) as actual
   from prdsal2
   group by state, product, year
   union
   select state, product, ., 'total by state and prodcuct', sum(actual)
   from prdsal2
   group by state, product
   union
   select state,'', year, 'total by state and year', sum(actual)
   from prdsal2
   group by state, year
   union
   select '',product, year, 'total by product and year', sum(actual)
   from prdsal2
   group by product, year
   union 
   select '' ,'', year, 'total by year', sum(actual)
   from prdsal2
   group by year
   union 
   select state, '',. , 'total by state', sum(actual)
   from prdsal2   
   group by state
   union 
   select '',  product, ., 'total by product', sum(actual)
   from prdsal2
   union
   select '', '', ., 'grand total', sum(actual)
   from prdsal2
   order by state, product, year
;quit;

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

Top 10 tips and tricks about PROC SQL

INTRODUCTION

PROC SQL is the implementation of the SQL syntax in SAS. It first appeared in SAS 6.0, and since then has been very popular for SAS users. SAS ships with a few sample data sets in its HELP library, and SASHELP.CLASS is one of them. This dataset contains 5 variables including name, weight, height, sex and age for 19 simulated teenagers, and in this paper I primarily use it for the demonstration purpose. Here I summarize the 10 interesting tricks and tips using PROC SQL. At the beginning, I first make a copy of SASHELP.CLASS at the WORK library and transform the row number of the data set to a new variable obs.

dataclass;

   setsashelp.class;

   /* Give an index for each child*/

   obs = _n_;

run;

1. Calculate the median of a variable

With the aggregating HAVING clause and some self-join techniques, PROC SQL can easily calculate the median for a variable.

procsql;

   selectavg(weight) as Median

   from(select e.weight

   fromclass e, class d

   groupby e.weight

   havingsum(case when e.weight = d.weight then 1else 0end)

      >= abs(sum(sign(e.weight - d.weight))));

quit;

2. Draw a horizontal histogram

A histogram visualizes the distribution pattern of a variable. PROC SQL can draw a horizontal histogram by showing the frequency bars with a few asterisks for each level of the variable age.

procsql;

   selectage, repeat('*',count(*)*4) as Frequency

   fromclass

   groupby age

   orderby age;

quit;

3. Return the running total for a variable

A running total is the summation of a sequence of numbers which is updated each time with the increase of the observations. In the example below, I calculate the running total and save them as a new variable Running_total by the SUM function and a conditional statement, which logically is similar to an example in SAS/IML[1]. 

procsql;

   selectname, weight,

      (selectsum(a.weight) from class as

      a wherea.obs <= b.obs) as Running_total

   fromclass as b;

quit;

4. Report the total number for a variable

PROC SQL is a flexible way to find the total number for any variable by its set operator UNION and the SUM function. In the example, the total number of the variable weight is reported at the bottom of the output table.

procsql;

   selectname, weight

   fromclass

   unionall

   select'Total', sum(weight)

   fromclass;

quit;

5. Retrieve the metadata for a data set

SAS stores the metadata at its DICTIONARY data sets. PROC SQL can visit the directory, retrieve the column detail, and return the information to the users.

procsql;

   selectname, type, varnum

   fromsashelp.vcolumn

   wherelibname = 'WORK' andmemname = 'CLASS';

quit;

6. Rank a variable 

Besides the designated ranking procedure PROC RANK in SAS, PROC SQL can also do some simple ranking as well.

procsql;

   selectname, a.weight, (select count(distinctb.weight)

   fromclass b

   /* Rank by the ascending order for the weight variable*/

   whereb.weight <= a.weight) as rank

   fromclass a;

quit;

7. Simple random sampling 

PROC SQL is widely used in simple random sampling. For example, I randomly choose 8 observations by the OUTOBS option at the PROC statement. The randomization process is realized by the RANUNI function at the ORDER BY statement with a seed 1234.

procsql outobs = 8;

   select*

   fromclass

   orderby ranuni(1234);

quit;

8. Replicate a data set without data

In PROC SQL, it is a fairly straightforward one-line statement to create a new empty data set while keeps all the structure of the original data set.

procsql;

   createtable class2 likeclass;

quit;

9. Transpose data

Usually DATA step ARRAY and PROC TRANSPOSE allow SAS users to restructure the data set, while PROC SQL sometimes is an alternative solution. For instance, if we need a wide-to-long operation to list the names of the children by their gender in the CLASS date set, then PROC SQL can fulfill the functionality through the combinations of some queries and subqueries.

procsql;

   selectmax(case when sex='F'

      thenname else ' ' end) as Female,

      max(casewhen sex='M'

      thenname else ' ' end) as Male

   from(select e.sex,

      e.name,

      (selectcount(*) from class d

      wheree.sex=d.sex and e.obs < d.obs) aslevel

      fromclass e)

   groupby level;

quit;

10. Count the missing values

Another advantage of PROC SQL is that its NMISS function works for both numeric and character variables [2], which makes PROC SQL an ideal tool for missing value detection.

procsql;

   selectcount(*) 'Total', nmiss(weight)

      'Number of missing values for weight'

   fromclass;

quit;

CONCLUSION

The combination of SAS’s powerful functions and the SQL procedure will benefit SAS users in data management and descriptive statistics.

10 keywords taken out from SAS Global Forum 2012

1. In-memory
SAS is famous for hitting hard disk at every operation, which is a proved strategy to save memory.  To speed up the processing of ‘Big Data’, SAS at the server side will aggregate memories, load data into memory and then deal with data there, which is 1000 times faster than the hard disk based operation.

2. Hadoop
Informationweek described that Dr. Goodnight, CEO of SAS, loathes Hadoop, the distributed open source platform. However, this time SAS presented its DI Studio and SAS/ACCESS interface, which now allows data access by Hive and Pig. It looks like a challenge for SAS to run its statistical procedures on HDFS.

3. Web application
SAS’s applications obviously start to move toward web, like SAS Visual Analytics, which also fits various mobile devices. There is a way to distinguish a desktop application and a web application in SAS: the former’s default background color is white and the latter is black.

4. High performance procedures
SAS is vigorously developing the procedures with HP as prefix, mostly for the servers. Currently 10-20 procedures in SAS/BASE and SAS/STAT can find their counterpart, such as PROC HPSummary and PROC HPLogistic. Those procedures can also run locally but won't not improve the efficiency significantly.

5. JMP
JMP remains as a lean desktop analysis package while SAS evolves toward gigantic enterprise solution platforms. One interesting thing -- you can always find the motion chart (which JMP can do and SAS can’t) and John Sall at the demo area.

6. SAS 12.1?
Next release of SAS is not 9.4, but 12.1. SAS version 9, including 9.1, 9.2 and 9.3, dwells for almost a decade. Thus, the version update from 9.3 to 12.1 is quite a great leap forward.
Correction - thanks to Chris Hemedinger, 'the new release numbering applies only to the analytical products (STAT, ETS, and so on)'.

7. Data Step 2?
To support data management along with the high performance procedures at the servers, a language called DS2 is under development. It is a strong typing language more like Java or C++ more than Data Step. However, SAS has a macro which can transform Data Step codes to Data Step 2.

Thanks to the corrections by Jason Secosky, who is the development manager for DS2 --
"While DS2 is based on the DATA step, its name is just "DS2" not "DATA step 2". DS2 is statically typed, not strongly typed. Ok, ok, there is no implicit type conversion between some types, like double and timestamp, yet there are functions to explicitly convert these values.
And, there is a PROC that can be used to translate DATA steps generated by SAS Enterprise Miner to DS2. The PROC isn't intended to convert *any* DATA step to DS2."

8. In-database
SAS’s in-database technology now supports all database systems beyond Teradata and Greenplum. To avoid compilation error, it is better to apply ANSI SQL functions instead of SAS’s own function. As for me, it is still not very clear how SAS passes its statistical procedures into the relation database systems.

9. Risk management
SAS’s risk management platform is quite mature and implemented the latest procedures like PROC COPULA. It seems that the end users have to own Bloomberg or other vendor’s license to update the market data.

10. Statistical graph
More SAS’s procedures and solution plans integrated layer-based statistical graph technology to visualize results. Still SAS’s Windowing Environment still doesn’t support the syntax highlighting for  Graph Template Language and the SG procedures, since it always shows red fonts warning syntax errors.

Stored Processes: SAS's voice on Business Intelligence

Everyday I write SAS scripts to extract, transform and load data from various sources, which is a step before the database, and also pull out data to do analysis such as aggregation and regression in SAS, which is a step after the database. According to Norman Nie, a data shop has a four-layer structure: ETL layer, database layer, analytics layer and BI layer. It seems that recently the database layer and the analytics layer look more and more identical. The relational databases start to fiercely create their statistics arms, such as the SQL Server Analysis Services and Oracle R Enterprise. Tricia and Angela’s new book, The 50 Keys to Learning SAS Stored Processes, discloses SAS’s revenge on Business intelligence layer with its stored processes, which are similiar to the stored procedures usually carried out by the database systems.

This book has 12 chapters, including how to create stored processes, optimize the stored processes and implement the stored processes. The book is easy to read and contains detailed illustrations with lots of colorful graphics. Following the examples, I may create my own stored processes in SAS, for example, a simple query to count numbers of a small data set SASHELP.CLASS by gender. It is also interesting to compare SAS’s stored process with the databases' stored procedure such as SQL Server -- they have the same functionality. Therefore, on a Windows server, a developer will be able to choose either SQL Server or SAS to build the web applications.

Now with the stored processes, a SAS programmer may evolve toward a SAS developer. Tricia and Angela’s book will be a good reference for this role transition.

/*********(1) LOAD DATA(SASHELP.CLASS) TO SQL SERVER OR SAS *************/
create table class (name char(8), sex char(1), 
age numeric, height numeric, weight numeric );
insert into class values('ALFRED','M',14,69,112.5);
insert into class values('ALICE','F',13,56.5,84);
insert into class values('BARBARA','F',13,65.3,98);
insert into class values('CAROL','F',14,62.8,102.5);
insert into class values('HENRY','M',14,63.5,102.5);
insert into class values('JAMES','M',12,57.3,83);
insert into class values('JANE','F',12,59.8,84.5);
insert into class values('JEFFREY','M',13,62.5,84);
insert into class values('JOHN','M',12,59,99.5);
insert into class values('JOYCE','F',11,51.3,50.5);
insert into class values('JUDY','F',14,64.3,90);
insert into class values('LOUISE','F',12,56.3,77);
insert into class values('MARY','F',15,66.5,112);
insert into class values('PHILIP','M',16,72,150);
insert into class values('ROBERT','M',12,64.8,128);
insert into class values('RONALD','M',15,67,133);
insert into class values('THOMAS','M',11,57.5,85);
insert into class values('WILLIAM','M',15,66.5,112);

/**********************(2) A STORED PROCEDURE IN SQL SERVER **************/
IF OBJECT_ID('CntBySex') IS NOT NULL
   drop proc CntBySex;
go

create proc CntBySex @sex char(1) = null, @Cnt int output
as
   select @Cnt = COUNT(*)
   from class
   where sex = ISNULL(@sex, sex)
go

declare @OutCnt int
exec CntBySex @Cnt = @OutCnt output
select @OutCnt as AllCnt

exec CntBySex @sex = 'F', @Cnt = @OutCnt output
select @OutCnt as FemaleCnt

exec CntBySex @sex = 'M', @Cnt = @OutCnt output
select @OutCnt as MaleCnt
go


/*************(3) A STORED PROCESS IN SAS *********************/
%macro query;
proc sql;
   select count(*) 
   from class
   %if %length(&sex) > 0 %then %do;
      where sex = &sex;
   %end; 
;
quit;
%mend query;

%stpbegin; 
   %query;
%stpend;

Correlations of three variables

Question
There is an interesting question in statistics --
“There are 3 random variables X, Y and Z. The correlation between X and Y is 0.8 and the
correlation between X and Z is 0.8. What is the maximum and minimum correlation between Y and Z?”

Solutions

1. Geometric illustration
The value of corr(Y, Z) is the COS function of the angle between Y and Z. We already know the corr(X, Y) and corr(X, Z). In this particular case, the angle can be zero, which suggests Y and Z are identical and the max value of corr(Y, Z) is 1. The min value of corr(Y, Z) is caused by the biggest angle between Y and Z, which is 0.28.

2. Positive semi-definiteness property of the correlation matrix
Due to this feature, the determinant of the correlation matrix is greater than or equal to zero. Thus we will be able to construct a quadratic inequality to evaluate the boundaries, which is from 0.28 to 1.

proc fcmp outlib=work.funcs.test1;
  function corrdet(x, a, b);
    return(-x**2 + 2*a*b*x - a**2 -b**2 +1);
  endsub;
  function solvecorr(ini, a, b);
    array solvopts[5] initial abconv relconv
          maxiter solvstat (.5 .001 1.0e-6 100);
    initial = ini;
    x = solve('corrdet', solvopts, 0, ., a, b);
    return(x);
  endsub;
quit;

options cmplib = work.funcs;
data one;
  * Max value;
  upper = solvecorr(1, 0.8, 0.8);
  upper_check = corrdet(upper,0.8,0.8);
  * Min value;
  lower = solvecorr(-1, 0.8, 0.8);
  lower_check = corrdet(lower,0.8,0.8);
run;

Generalization
We can generalize the question to all possibilities for corr(X, Y) and corr(X, Z). First we need to create two user-defined functions to solve the maximum and the minimum values. Then we will be able to draw the max values and min values in the same plot. It is very interesting to see that only four points the upper surface and lower surface converge together, which are (1, 1, 1), (-1, 1, -1), (1, -1, 1) and (-1, -1, -1).

A lot other phenomenon can be summarized from this plot, such as that when corr(X, Y) = corr(X, Z) the max value of corr(Y, Z) is always equal to 1.

proc fcmp outlib = work.funcs.test2;
  function upper(a, b);
     x = 4*(a**2)*(b**2) - 4*(a**2+b**2-1);
     if x ge 0 then y = -0.5*(sqrt(x) - 2*a*b);
     else y = .;
     return(y);
  endsub;
  function lower(a, b);
     x = 4*(a**2)*(b**2) - 4*(a**2+b**2-1);
     if x ge 0 then y = -0.5*(-sqrt(x) - 2*a*b);
     else y = .;
     return(y);
  endsub;
quit;

data two;
   do xy = -.99 to .99 by 0.01;
      do xz = -.99 to .99 by 0.01;
         upper = upper(xy, xz);
         lower = lower(xy, xz);
         output;
      end;
   end;
run;

proc template;
   define statgraph surface001;
   begingraph;
      layout overlay3d / cube = false rotate = 150 tilt = 30
         xaxisopts = (label="Correlation between X and Y") 
         yaxisopts = (label="Correlation between X and Z") 
         zaxisopts = (label="Boundaries of correlation between Y and Z") ;
      surfaceplotparm x = xy y = xz z = upper; 
      surfaceplotparm x = xy y = xz z = lower; 
      endlayout;
   endgraph;
   end;
run;

proc sgrender data =  two template = surface001;
run;