A macro to execute PROC TTEST for multiple binary grouping variables in SAS (and sorting t-test statistics by their absolute values)

In SAS, you can perform PROC TTEST for multiple numeric variables in the same procedure.  Here is an example using the built-in data set SASHELP.BASEBALL; I will compare the number of at-bats and number of walks between the American League and the National League.

proc ttest
data = sashelp.baseball;
class League;
var nAtBat nBB;
ods select ttests;
run;

Here are the resulting tables.

Method Variances DF t Value Pr > |t|
Pooled Equal 320 2.05 0.0410
Satterthwaite Unequal 313.66 2.06 0.04

Method Variances DF t Value Pr > |t|
Pooled Equal 320 0.85 0.3940
Satterthwaite Unequal 319.53 0.86 0.3884

What if you want to perform PROC TTEST for multiple grouping (a.k.a. classification) variables?  You cannot put more than one variable in the CLASS statement, so you would have to run PROC TTEST separately for each binary grouping variable.  If you do put LEAGUE and DIVISION in the same CLASS statement, here is the resulting log.

1303 proc ttest
1304 data = sashelp.baseball;
1305 class league division;
--------
22
202
ERROR 22-322: Expecting ;.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
1306 var natbat;
1307 ods select ttests;
1308 run;

There is no syntax in PROC TTEST to use multiple grouping variables at the same time, so this tutorial provides a macro to do so.  There are several nice features about my macro:

1. It allows you to use multiple grouping variables at the same time.
2. It sorts the t-test statistics by their absolute values within each grouping variable.
3. It shows the name of each continuous variable in the output table, unlike the above output.

Here is its basic skeleton.

Use ODS EXCLUDE ALL to suppress printing output in SAS while producing output data sets

I regularly produce output data sets from a SAS procedure, such as getting the variable names from a data set in PROC CONTENTS.  In these instances, I often wish to suppress any printing of the output in HTML or TXT.  Such printing of the results is often unnecessary, and it can cost a lot of time and memory.

Some SAS procedures have the NOPRINT option that suppresses the printing of output, but this is limiting in several ways:

1. Some SAS procedures do NOT have the NOPRINT option.  PROC TTEST is a prominent example.  I checked the high-performance procedures like PROC HPFOREST (random forest) and PROC HPSVM (support vector machine), and I could not find the NOPRINT option for these procedures.
2. I cannot use ODS OUTPUT to produce output data sets while invoking the NOPRINT option.  Here is an example.

Convert multiple variables between character and numeric formats in SAS

Introduction

I often get data that are coded as character, but are actually meant to be numeric.  Thus, converting them into the correct variable types is a common task, and SAS Note #24590 shows how to do so.  However, I recently needed to do hundreds of these conversions, so I wanted some code to accomplish this quickly and accurately.  This tutorial shows how to do so.

Let’s consider this small data set in SAS as an example.  They are hypothetical statistics of 3 players from a basketball game.

data basketball1;
input jersey points $rebounds$ assists $; datalines; 21 10 14 1 4 11 3 12 23 29 4 5 ; run; The 3 performance metrics (points, rebounds, and assists) are clearly numeric, but they are currently coded as character. (You can use PROC CONTENTS to confirm this if needed.) The jersey number is really a character variable, because its magnitude has no real-life meaning. The National Basketball Association (NBA) allows “00” as a possible jersey number. (Robert Parish wore this jersey number; he won 4 NBA championships and reached the Naismith Basketball Hall of Fame.) If you code “00” as a numeric variable, then it will render as “0”. Thus, for NBA jersey numbers, it is best to save it as a character variable. I can convert these variables into the correct types using the following code. Note that I chose “2.” for the length of “JERSEY”, because I know that jersey numbers in the NBA have, at most, 2 digits. data basketball2; set basketball1; jersey2 = put(jersey, 2.); drop jersey; rename jersey2 = jersey; points2 = input(points, 8.); drop points; rename points2 = points; rebounds2 = input(rebounds, 8.); drop rebounds; rename rebounds2 = rebounds; assists2 = input(assists, 8.); drop assists; rename assists2 = assists; run; Despite this success, the above code can be very cumbersome when I need to do this for many variables, and this situation arose in my job recently. In this tutorial, I will show a fast way of doing these conversions for many variables at once. I will use this BASKETBALL1 data set as an example, and I will convert POINTS, REBOUNDS, and ASSISTS from character to numeric simultaneously. A macro to automate the creation of indicator variables in SAS In a recent blog post, I introduced an easy and efficient way to create indicator variables from categorical variables in SAS. This method pretends to run logistic regression, but it really is using PROC LOGISTIC to get the design matrix based on dummy-variable coding. I shared SAS code for how to do so, step-by-step. I write this follow-up post to provide a macro that you can use to execute all of those steps in one line. If you have not read my previous post on this topic, then I strongly encourage you to do that first. Don’t use this macro blindly. Here is the macro. The key steps are 1. Run PROC LOGISTIC to get the design matrix (which has the indicator variables) 2. Merge the original data with the newly created indicator variables 3. Delete the “INDICATORS” data set, which was created in an intermediate step %macro create_indicators(input_data, target, covariates, output_data); proc logistic data = &input_data noprint outdesign = indicators; class &covariates / param = glm; model &target = &covariates; run; data &output_data; merge &input_data indicators (drop = Intercept &target); run; proc datasets library = work noprint; delete indicators; run; %mend; I will use the built-in data set SASHELP.CARS to illustrate the use of my macro. As you can see, my macro can accept multiple categorical variables as inputs for creating indicator variables. I will do that here for the variables TYPE, MAKE, and ORIGIN. An easy and efficient way to create indicator variables (a.k.a. dummy variables) from a categorical variable in SAS Introduction In statistics and biostatistics, the creation of binary indicators is a very useful practice. • They can be useful predictor variables in statistical models. • They can reduce the amount of memory required to store the data set. • They can treat a categorical covariate as a continuous covariate in regression, which has certain mathematical conveniences. However, the creation of indicator variables can be a long, tedious, and error-prone process. This is especially true if there are many categorical variables, or if a categorical variable has many categories. In this tutorial, I will show an easy and efficient way to create indicator variables in SAS. I learned this technique from SAS usage note #23217: Saving the coded design matrix of a model to a data set. The Example Data Set Let’s consider the PRDSAL2 data set that is built into the SASHELP library. Here are the first 5 observations; due to a width constraint, I will show the first 5 columns and the last 6 columns separately. (I encourage you to view this data set using PROC PRINT in SAS by yourself.) COUNTRY STATE COUNTY ACTUAL PREDICT U.S.A. California$987.36 $692.24 U.S.A. California$1,782.96 $568.48 U.S.A. California$32.64 $16.32 U.S.A. California$1,825.12 $756.16 U.S.A. California$750.72 $723.52 PRODTYPE PRODUCT YEAR QUARTER MONTH MONYR FURNITURE SOFA 1995 1 Jan JAN95 FURNITURE SOFA 1995 1 Feb FEB95 FURNITURE SOFA 1995 1 Mar MAR95 FURNITURE SOFA 1995 2 Apr APR95 FURNITURE SOFA 1995 2 May MAY95 Remove leading blanks when creating macro variables using PROC SQL in SAS I regularly use PROC SQL to create macro variables in SAS, and I recently noticed a strange phenomenon when resolving a macro variable within double quotation marks in the title of a plot. Thankfully, I was able to replicate this problem using the SASHELP.BASEBALL data set, which is publicly available. I was then able to send the code and the strange result to SAS Technical Support for their examination. proc sql; select count(name) into :hitters_100plusHR from sashelp.baseball where CrHome > 100; quit; proc sgplot data = sashelp.baseball; histogram Salary; title1 'Distribution of salaries'; title2 "Restricted to the &hitters_100plusHR hitters with more than 100 career home runs"; run; Here is the resulting plot. Notice the extra spaces before “72” in the title of the plot. SAS Technical Support informed me that • this problem is commonly known. • there is no way of predicting when it will occur • for now, the best way to deal with it is to remove the leading blanks using one of several ways. A SAS macro to automatically label variables using another data set Introduction When I write SAS programs, I usually export the analytical results into an output that a client will read. I often cannot show the original variable names in these outputs; there are 2 reasons for this: • The maximal length of a SAS variable’s name is 32 characters, whereas the description of the variable can be much longer. This is the case for my current job in marketing analytics. • Only letters, numbers, and underscores are allowed in a SAS variable’s name. Spaces and special characters are not allowed. Thus, if a variable’s name is quite long and complicated to describe, then the original variable name would be not suitable for presentation or awkward to read. It may be so abbreviated that it is devoid of practical meaning. This is why labelling variables can be a good idea. However, I usually label variables manually in a DATA step or within PROC SQL, which can be very slow and prone to errors. I recently worked on a data set with 193 variables, most of which require long descriptions to understand what they mean. Labelling them individually and manually was not a realistic method, so I sought an automated or programmatic way to do so. Store multiple strings of text as a macro variable in SAS with PROC SQL and the INTO statement I often need to work with many variables at a time in SAS, but I don’t like to type all of their names manually – not only is it messy to read, it also induces errors in transcription, even when copying and pasting. I recently learned of an elegant and efficient way to store multiple variable names into a macro variable that overcomes those problems. This technique uses the INTO statement in PROC SQL. To illustrate how this storage method can be applied in a practical context, suppose that we want to determine the factors that contribute to a baseball player’s salary in the built-in SASHELP.BASEBALL data setI will consider all continuous variables other than “Salary” and “logSalary”, but I don’t want to write them explicitly in any programming statements. To do this, I first obtain the variable names and types of a data set using PROC CONTENTS. * create a data set of the variable names; proc contents data = sashelp.baseball noprint out = bvars (keep = name type); run; Use the LENGTH statement to pre-set the lengths of character variables in SAS – with a comparison to R I often create character variables (i.e. variables with strings of text as their values) in SAS, and they sometimes don’t render as expected. Here is an example involving the built-in data set SASHELP.CLASS. Here is the code: data c1; set sashelp.class; * define a new character variable to classify someone as tall or short; if height > 60 then height_class = 'Tall'; else height_class = 'Short'; run; * print the results for the first 5 rows; proc print data = c1 (obs = 5); run; Here is the result: Obs Name Sex Age Height Weight height_class 1 Alfred M 14 69.0 112.5 Tall 2 Alice F 13 56.5 84.0 Shor 3 Barbara F 13 65.3 98.0 Tall 4 Carol F 14 62.8 102.5 Tall 5 Henry M 14 63.5 102.5 Tall What happened? Why does the word “Short” render as “Shor”? Getting the names, types, formats, lengths, and labels of variables in a SAS data set After reading my blog post on getting the variable names of a SAS data set, a reader named Robin asked how to get the formats as well. I asked SAS Technical Support for help, and a consultant named Jerry Leonard provided a beautiful solution using PROC SQL. Besides the names and formats of the variables, it also gives the types, lengths, and labels. Here is an example of how to do so with the CLASS data set in the built-in SASHELP library. * add formats and labels to 3 of the variables in the CLASS data set; data class; set sashelp.class; format age 8. weight height 8.2 name$15.;
label
age = 'Age'
weight = 'Weight'
height = 'Height';
run;

* extract the variable information using PROC SQL;
proc sql
noprint;
create table class_info as
select libname as library,
memname as data_set,
name as variable_name,
type,
length,
format,
label
from dictionary.columns
where libname = 'WORK' and memname = 'CLASS';
/* libname and memname values must be upper case  */
quit;

* print the resulting table;
proc print
data = class_info;
run;

Here is the result of that PROC PRINT step in the Results Viewer.  Notice that it also has the type, length, format, and label of each variable.

Obs library data_set variable_name type length format label
1 WORK CLASS Name char 8 $15. 2 WORK CLASS Sex char 1 3 WORK CLASS Age num 8 8. Age 4 WORK CLASS Height num 8 8.2 Height 5 WORK CLASS Weight num 8 8.2 Weight Thank you, Jerry, for sharing your tip! Sorting correlation coefficients by their magnitudes in a SAS macro Theoretical Background Many statisticians and data scientists use the correlation coefficient to study the relationship between 2 variables. For 2 random variables, $X$ and $Y$, the correlation coefficient between them is defined as their covariance scaled by the product of their standard deviations. Algebraically, this can be expressed as $\rho_{X, Y} = \frac{Cov(X, Y)}{\sigma_X \sigma_Y} = \frac{E[(X - \mu_X)(Y - \mu_Y)]}{\sigma_X \sigma_Y}$. In real life, you can never know what the true correlation coefficient is, but you can estimate it from data. The most common estimator for $\rho$ is the Pearson correlation coefficient, which is defined as the sample covariance between $X$ and $Y$ divided by the product of their sample standard deviations. Since there is a common factor of $\frac{1}{n - 1}$ in the numerator and the denominator, they cancel out each other, so the formula simplifies to $r_P = \frac{\sum_{i = 1}^{n}(x_i - \bar{x})(y_i - \bar{y})}{\sqrt{\sum_{i = 1}^{n}(x_i - \bar{x})^2 \sum_{i = 1}^{n}(y_i - \bar{y})^2}}$. In predictive modelling, you may want to find the covariates that are most correlated with the response variable before building a regression model. You can do this by 1. computing the correlation coefficients 2. obtaining their absolute values 3. sorting them by their absolute values. Analyst Finder – A Free Job-Matching Service for Statisticians, Data Scientists, Database Managers and Data Analysts If you are a statistician, data scientist, database manager, or data analyst, then consider using Analyst Finder for your next job search. It is a web site that connects job seekers in data analytics with employers. The service is free for job seekers, and it earns money by charging companies and recruiters a small fee to find qualified candidates through its job-matching service. To register for this service as a job seeker, you simply need to complete a check list of skills and preferences. It’s quick and easy to do, and you can change this list whenever your wish to update your qualifications. Employers and recruiters can register for this service to search for qualified candidates, and the fees are displayed on the web site. This company was founded by Art Tabachneck, a former president of the Toronto Area SAS Society and a veteran analytics professional. In case you’re interested in learning more about him, SAS has a profile about him in recognition of his expertise in SAS programming and his contributions to online support communities and user groups. New Job at the Bank of Montreal in Toronto I have accepted an offer from the Bank of Montreal to become a Manager of Operational Risk Analytics and Modelling at its corporate headquarter office in Toronto. Thus, I have resigned from my job at the British Columbia Cancer Agency. I will leave Vancouver at the end of December, 2015, and start my new job at the beginning of January, 2016. I have learned some valuable skills and met some great people here in Vancouver over the past 2 years. My R programming skills have improved a lot, especially in text processing. My SAS programming skills have improved a lot, and I began a new section on my blog to SAS programming as a result of what I learned. I volunteered and delivered presentations for the Vancouver SAS User Group (VanSUG) – once on statistical genetics, and another on sampling strategies in analytical chemistry, ANOVA, and PROC TRANSPOSE. I have thoroughly enjoyed meeting some smart and helpful people at the Data Science, Machine Learning, and R Programming Meetups. I lived in Toronto from 2011 to 2013 while pursuing my Master’s degree in statistics at the University of Toronto and working as a statistician at Predicum. I look forward to re-connecting with my colleagues there. Vancouver SAS User Group Meeting – Wednesday, November 4, 2015 I am excited to present at the next Vancouver SAS User Group (VanSUG) meeting on Wednesday, November 4, 2015. I will illustrate data transposition and ANOVA in SAS and JMP using potato chips and analytical chemistry. Come and check it out! The following agenda contains all of the presentations, and you can register for this meeting on the SAS Canada web site. This meeting is free, and a free breakfast will be served in the morning. Update: My slides from this presentation have been posted on the VanSUG web site. Date: Wednesday, November 4, 2015 Place: Ballroom West and Centre Holiday Inn – Vancouver Centre 711 West Broadway, Vancouver, BC V5Z 3Y2 (604) 879-0511 Agenda: 8:30am – 9:00am: Registration 9:00am – 9:20am: Introductions and SAS Update – Matt Malczewski, SAS Canada 9:20am – 9:40am: Lessons On Transposing Data, Sampling & ANOVA in SAS & JMP – Eric Cai, Cancer Surveillance & Outcomes, BC Cancer Agency 9.40am – 10.20am: Make SAS Enterprise Guide Your Own – John Ladds, Statistics Canada 10:20am – 10:30am: A Beginner’s Experience Using SAS – Kim Burrus, Cancer Surveillance & Outcomes, BC Cancer Agency 10:30am – 11:00am: Networking Break 11:00am – 11.20am: Using SAS for Simple Calculations – Jay Shurgold, Rick Hansen Institute 11:20am – 11:50am: Yes, We Can… Save SAS Formats – John Ladds, Statistics Canada 11:50am – 12:20pm: Reducing Customer Attrition with Predictive Analytics – Nate Derby, Stakana Analytics 12:20pm – 12:30pm: Evaluations, Prize Draw & Closing Remarks If you would like to be notified of upcoming SAS User Group Meetings in Vancouver, please subscribe to the Vancouver SAS User Group Distribution List. How to Extract a String Between 2 Characters in R and SAS Introduction I recently needed to work with date values that look like this:  mydate Jan 23/2 Aug 5/20 Dec 17/2 I wanted to extract the day, and the obvious strategy is to extract the text between the space and the slash. I needed to think about how to program this carefully in both R and SAS, because 1. the length of the day could be 1 or 2 characters long 2. I needed a code that adapted to this varying length from observation to observation 3. there is no function in either language that is suited exactly for this purpose. In this tutorial, I will show you how to do this in both R and SAS. I will write a function in R and a macro program in SAS to do so, and you can use the function and the macro program as you please! Eric’s Enlightenment for Friday, May 29, 2015 1. P2N3: An aromatic ion made of just phosphorous and nitrogen. (Yes, aromaticity can be entirely inorganic!) 2. Using 3-D printing and plastics to make prosthetics. 3. David Beckwroth and Scott Sumner talk at length about reforming monetary policy with NGDP targeting in this video interview/seminar. 4. Anky Lai gives a nice introduction to PROC TABULATE (PDF document) – an alternative to PROC FREQ and PROC MEANS in SAS. Check out her awesome code samples for generating nicely formatted tables and exporting them conveniently into spreadsheets in Excel! Eric’s Enlightenment for Wednesday, May 6, 2015 1. Moldova has mysteriously lost one-eighth of its GDP, possibly to fraudulent loans. 2. Kai Brothers was diagnosed with HIV in 1989, but did not show any symptoms for 25 years. Does he have a natural defense against HIV? Now that he is starting to show symptoms, should he start taking anti-retroviral drugs and deny scientists the chance to look for that natural defense in his blood? 3. Use the VVALUE function in SAS to convert formatted values of a variable into new values of a character variable. 4. Alex Reinhart diligently compiled and explained a list of major “egregious statistical fallacies regularly committed in the name of science”. Check them out on his web site and in his book entitled “Statistics Done Wrong“. I highly recommend reading the section entitled “The p value and the base rate fallacy“. Eric’s Enlightenment for Friday, May 1, 2015 1. PROC GLIMMIX Contrasted with Other SAS Statistical Procedures for Regression (including GENMOD, MIXED, NLMIXED, LOGISTIC and CATMOD). 2. Lee-Ping Wang et al. recently developed the nanoreactor, “a computer model that can not only determine all the possible products of the Urey-Miller experiment, but also detail all the possible chemical reactions that lead to their formation”. What an exciting development! It “incorporates physics and machine learning to discover all the possible ways that your chemicals might react, and that might include reactions or mechanisms we’ve never seen before”. Here is the original paper. 3. A Quora thread on the best examples of the Law of Unintended Consequences 4. In a 2-minute video, Alex Tabarrok argues why software patents should be eliminated. Eric’s Enlightenment for Friday, April 24, 2015 1. Anna Katherine Barnett-Hart wrote an empirical study of how collateralized debt obligations contributed to$542 billion in losses suffered by financial institutions during the sub-prime mortgage crisis.  This was her honours thesis for her Bachelor of Arts degree at Harvard.  Here is her interview about her work with Dylan Ratigan.
2. Instead of money, Toyota offered its engineers to help The Food Bank for New York City to improve its operations.  Thanks to their guidance, the wait time for dinner was cut from 90 minutes to 18 minutes.
3. Unusual, simple, yet effective: Dispensing with loyalty cards or rewards programs, Pret A Manger spontaneously offers free food to reward loyal customers.
4. A fast and simple macro for getting the sample size of a data set in SAS.  (No need to scroll through PROC CONTENTS in case you have many variables.)
5. Jon Chui provides some useful pictorial guides for interpreting infrared spectra and proton nuclear magnetic resonance spectra.