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;