Resources

Creating and Using Tables

/****************************************************************/
/*          S A S   S A M P L E   L I B R A R Y                 */
/*                                                              */
/*    NAME: Tables.sas                                          */
/*   TITLE: Creating and Using Tables                           */
/* PRODUCT: IML                                                 */
/*  SYSTEM: ALL                                                 */
/*                                                              */
/* SUPPORT: Rick Wicklin                UPDATE: July 2016       */
/*     REF:                                                     */
/*    MISC:                                                     */
/* Modules:                                                     */
/*                                                              */
/****************************************************************/

/************************************/
/* Create Tables from SAS Data Sets */
/************************************/
proc iml;
tClass = TableCreateFromDataSet("Sashelp", "Class");

nrow = nrow(tClass);
ncol = ncol(tClass);
print nrow ncol;

dsOpt = "drop=Name rename=(Weight=Mass) where=(Sex='M')";
tblBoys = TableCreateFromDataSet("Sashelp", "Class", dsOpt);
quit;

/*******************************/
/* Create Tables from Matrices */
/*******************************/
proc iml;
Hurr  = {"Katrina", "Ike",       "Andrew", "Wilma"};
Month = {"August",  "September", "August", "October"};
tbl = TableCreate({"Name" "Month"}, Hurr||Month);

Yr   = {2005, 2008, 1992, 2005};
Wind = { 175,  145,  175,  185};   /* max wind, mph */
call TableAddVar(tbl, {"Year" "MaxWind"}, Yr||Wind);  /* add numeric cols */

colNames = TableGetVarName(tbl);
print colNames;
quit;

/******************************/
/* Query Properties of Tables */
/******************************/
proc iml;
tClass = TableCreateFromDataSet("Sashelp", "Class");
colnames = TableGetVarName(tClass);
type = TableGetVarType(tClass);
isNum = TableIsVarNumeric(tClass);
print (type // char(isNum))[c=colnames r={"Type" "IsNum"}];
quit;

/*****************************************/
/* Extract Data from a Table to Matrices */
/*****************************************/
proc iml;
tClass = TableCreateFromDataSet("Sashelp", "Class");
colNames = {"Height" "Weight"};
X = tableGetVarData(tClass, colNames);
cov = cov(X);
print cov[c=colNames r=colNames];
quit;

/*****************/
/* Modify Tables */
/*****************/
proc iml;
/* Compute BMI from the Height and Weight vars   */
start AddBMI(tbl);
   weight = TableGetVarData(tbl, "Weight");
   height = TableGetVarData(tbl, "Height");
   BMI = weight / height##2 * 703;    /* standard formula */
   call TableAddVar(tbl, "BMI", BMI); /* add numeric col */
   return;
finish;

tClass = TableCreateFromDataSet("Sashelp", "Class");
run AddBMI(tClass);          /* call a module that modifies the table */
bmi = TableGetVarData(tClass, "BMI");
call histogram(bmi);         /* graph distribution of derived column */
quit;

/********************************/
/* Save Tables to SAS Data Sets */
/********************************/
proc iml;
tbl = TableCreate("Letters", T("A":"Z"));  /* create a character column   */
call TableAddvar(tbl, "ID", T(1:26));      /* create a numeric column     */
call TableWriteToDataSet(tbl, "work", "Letters");  /* create SAS data set */

vars = contents("Letters");                /* variables in work.Letters   */
print vars;

call TableWriteToDataSet(tbl, "work", "Letters2",
                         "where=(mod(ID,2)=0)");  /* write subset */
quit;


/************************/
/* Release Table Memory */
/************************/
proc iml;
tClass = TableCreateFromDataSet("Sashelp", "Class");
tNew = tClass;        /* make a copy of the table */
quit;

proc iml;
x = 1:10;
y = {1 2, 3 4};
tbl = TableCreateFromDataSet("Sashelp", "BWeight");   /* load a big table */
free tbl;                                         /* delete the big table */
show names;
quit;

/********************************************/
/* Compute Statistics for Columns of Tables */
/********************************************/
proc iml;
start PrintDescStats( tbl );
   cols = loc( TableIsVarNumeric(tbl) ); /* get column numbers */
   if ncol(cols)=0 then do;
      print "The table does not contain any numeric columns.";
      return;
   end;
   stats = j(5, ncol(cols));            /* allocate matrix for results */
   m = TableGetVarData(tbl, cols);      /* extract data into matrix */
   stats[1,] = countn(m);               /* N       for each column  */
   stats[2,] = mean(m);                 /* Mean    for each column  */
   stats[3,] = std(m);                  /* Std Dev for each column  */
   stats[4,] = m[><, ];                 /* Minimum for each column  */
   stats[5,] = m[<>, ];                 /* Maximum for each column  */
   varNames = TableGetVarName(tbl, cols);
   rowNames = {"N", "Mean", "Std Dev", "Minimum", "Maximum"};
   print stats[L="Descriptive Statistics" r=rowNames c=varNames];
finish;

table = TableCreateFromDataSet("Sashelp", "Class");
run PrintDescStats(table);
quit;

/****************/
/* Print Tables */
/****************/
proc iml;
tClass = TableCreateFromDataSet("Sashelp", "Class");
call TablePrint(tClass) label="Subset of Students"
                        var={"Name" "Sex" "Age" "Weight"}
                        justify={'R' 'C' 'C' 'R'}
                        ID="Name"
                        firstObs=3 numobs=5;
quit;


/**************************/
/* Using Custom Templates */
/**************************/
proc template;
define table Custom1;
   column Estimate LowerCL UpperCL;     /* names and order of columns */
   define header topHeader;             /* header at top of table */
      text "Parameter Estimates";
   end;
   define header SpanHeader;            /* define spanning header */
      text "95% Confidence Limits";     /* title of spanning header */
      start = LowerCL;                  /* span starts at second column */
      end   = UpperCL;                  /* span ends at third column */
   end;
end;
run;


proc iml;
x = {1   0.5 1.5,
     1.8 1.6 2.0,
     2.8 2.1 3.5};
ParamEst = TableCreate({"Estimate" "LowerCL" "UpperCL"}, x);
call TablePrint(ParamEst) template="Custom1";
quit;

proc template;
define table CustomColor1;
   cellstyle _COL_ = 2 && _VAL_="M" as {backgroundcolor=LightBlue},
             _COL_ = 2 && _VAL_="F" as {backgroundcolor=Pink},
             _COL_ = 5 && _VAL_>100 as {backgroundcolor=LightOrange};
   end;
run;

proc iml;
tbl = TableCreateFromDataSet("sashelp", "class");
call TablePrint(tbl) numobs=6
                     template="CustomColor1";
quit;


/***********************************************/
/* Using Dynamic Variables in Custom Templates */
/***********************************************/
proc template;
define table CustomColor2;
   dynamic MaleColor FemaleColor OverweightColor;
   cellstyle _COL_ = 2 && _VAL_="M" as {backgroundcolor=MaleColor},
             _COL_ = 2 && _VAL_="F" as {backgroundcolor=FemaleColor},
             _COL_ = 5 && _VAL_>100 as {backgroundcolor=OverweightColor};
end;
run;

proc iml;
tbl = TableCreateFromDataSet("sashelp", "class");
M = "LightGreen";
F = "LightRed";
Wt = "LightGrey";
/* Syntax: DynamicVar1=Symbol1, DynamicVar2=Symbol2, ...  */
dynamicVar = {"MaleColor=M" "FemaleColor=F" "OverweightColor=Wt"};
call TablePrint(tbl) numobs=6
                     template="CustomColor2"
                     dynamic=dynamicVar;

MaleColor = "LightGreen";
FemaleColor = "LightRed";
OverweightColor = "LightGrey";
/* names of SAS/IML symbols = names of dynamic variables in template */
dynamicVar = {"MaleColor" "FemaleColor" "OverweightColor"};
call TablePrint(tbl) numobs=6
                     template="CustomColor2"
                     dynamic=dynamicVar;


/**************************/
/* Using Column Templates */
/**************************/
proc template;
define table ColTempl1;
   header _LABEL_;              /* main header */
   define column Generic;
      generic=ON;               /* multiple variables can use */
   end;
   /* column for numeric variables */
   define column Float;
      generic=ON;               /* multiple variables can use */
      format=7.2;               /* display in 7.2 format */
      style=DataStrong;         /* boldface */
      cellstyle _COL_ as {color=Blue};  /* blue text */
   end;
   /* column for numeric integer variables */
   define column Integer;
      generic=ON;               /* multiple variables can use */
      format=6.;                /* display in 6. format */
      cellstyle _COL_ as {color=Red backgroundcolor=GrayEE};
      justify=ON; just=C;       /* center text */
   end;
end;
run;

proc iml;
tbl = TableCreateFromDataSet("sashelp", "class");
call TablePrint(tbl)
     label="Custom Column Templates"
     numobs=6
     template="ColTempl1"
     var        ={"Name" "Height" "Weight" "Age"}
     coltemplate={Generic Generic Float Integer};


/***********************************************/
/* Using Dynamic Variables in Column Templates */
/***********************************************/
proc template;
define table ColTempl2;
   header _LABEL_;
   /* column for character variables */
   define column Generic;
      generic=ON;         /* multiple variables can use */
   end;
   /* column for numeric variables */
   define column Float;
      dynamic ColFormat="7.2"
              ColStyle="DataStrong"
              ColTextColor="Blue";
      generic=ON;         /* multiple variables can use */
      format=ColFormat;
      style=ColStyle;
      cellstyle _COL_ as {color=ColTextColor};
   end;
   define column Integer;
      dynamic ColTextColor="Red"
              ColBGColor="GrayEE";
      generic=ON;         /* multiple variables can use */
      format=6.;
      cellstyle _COL_ as {color=ColTextColor backgroundcolor=ColBGColor};
      justify=ON; just=C; /* center text */
   end;
end;
run;

proc iml;
tbl = TableCreateFromDataSet("sashelp", "class");
varNames     = {"Name" "Height" "Weight" "Age"};
colTemplates = {Generic Generic Float Integer};

Fmt="8.3";              /* specify one value for Float column template */
ColBGColor="LightPink"; /* specify two values for Integer column template */
ColTextColor="Black";

DynValues = {" "  " "             /* no dynamic vals for Generic columns */
             "ColFormat=Fmt"                         /* vals for Float   */
             "ColBGColor ColTextColor"};             /* vals for Integer */
call TablePrint(tbl)
     label="Column Templates with Dynamic Variables"
     numobs=6
     template="ColTempl2"
     var=varNames
     coltemplate=ColTemplates
     coldynamic=DynValues;
quit;


/********************************/
/* Using Existing SAS Templates */
/********************************/
ods trace on;
proc reg data=sashelp.class plots=none;
   model height=weight age / CLB;
   ods output ParameterEstimates=Parms;
   ods select ParameterEstimates;
quit;
ods trace off;

proc iml;
tbl = TableCreateFromDataSet("Parms");
call TablePrint(tbl) colheader="Names";

Confidence=95;
call TablePrint(tbl) template="Stat.Reg.ParameterEstimates"
                     dynamic={Confidence};
quit;