sas programming

7月 262017
 

Trivial Pursuit, Justin Bieber and Timbits. Some pretty great things have come from Canada, eh? Well, you can go ahead and add expert SAS programmers to that impressive list.

In this video, six Canadian SAS programmers, with more than 115 years of SAS programming experience between them, share some of their favorite, little-known SAS programming tips. You're sure to discover a new trick or two.

The video includes the following tips and more:

  • Standardizing and documenting your SAS program.
  • Creating parameter lookup tables.
  • Declaring your macro variables.
  • Using the Characterize Data task in SAS Enterprise Guide.
  • Data exploration best practices for SAS Enterprise Guide.

Looking for more great tips to help bring your SAS programming skills to the next level? Check out these great resources and learn even more from your SAS peers:

  • SAS Support Communities: Peer-to-peer support for SAS users about programming, data analysis, installation and deployment issues, tips and best practices and a whole lot more.
  • SAS Blogs: Connecting you to people, products and ideas from SAS with technical tips, support information and more.
  • SAS Newsletters: The latest news, tips, tricks and resources from SAS, plus advice and industry knowledge gleaned from top experts and other SAS users.

 

Programming tips from experienced SAS users was published on SAS Users.

7月 182017
 

Datasets are rarely ready for analysis, and one of the most prevalent problems is missing data. This post is the first in a short series focusing on how to think about missingness, how JMP13 can help us determine the scope of missing data in a given table, and how to [...]

The post How severe is your missing data problem? appeared first on SAS Learning Post.

7月 032017
 

If you spend a lot of time in SAS Enterprise Guide (as I do), you probably get to know its features pretty well. But we don't always take the time to explore as we should, so there might be a few golden nuggets of editor knowledge that have escaped you so far. Here are 10 program editor features that I've found essential while writing, editing, and debugging SAS programs. How many of these do you already know and use?

1. Turn on the line numbers

We programmers like to count lines of code. The SAS log often uses line numbers to reference problems in WARNINGs and ERRORs. So of course, you should have line numbers displayed in the program editor. But they aren't on by default. Go to Program → Editor Options and select "Show line numbers" to turn them on.

line numbers

2. Get the tabs out (or leave them in)

Tabs or spaces? Your choice here can have a significant effect on your earning potential, and perhaps even on your love life. Most code editors have options that support your choice, regardless of which camp you choose. SAS Enterprise Guide offers these:

  • Tab size - width of a tab character, represented in number of spaces. Default is 4, but I like to use 2 as it makes my program lines less wide.
  • Insert spaces for tabs - when you press the TAB key, don't add a TAB character but instead add the specified number of space characters.
  • Replace tabs with spaces on file open - a perfect passive-aggressive option when working with team members who disagree with your TAB world view. This option will change TAB characters to spaces when you open the program file. If you must retain the TAB characters...well, my main advice is do not rely on TAB characters in your code file. But if you must for some crazy reason, don't select this option and sign a pact with your teammates for the same.

tabs vs spaces

3. Define abbreviations for commonly used code

The most common code snippet that I reuse is a LIBNAME statement that points to my project data. To save on typing and mistakes, I've saved this as an editor abbreviation. When I begin to type the alias I've assigned to the snippet, the program editor offers to complete it for me. The custom abbreviation is presented along side all of the other built-in syntax suggestions.

abbrev animation

See more about editor abbreviations in this article.

4. Let the editor format your code

As shown in the vigorous "TABS vs spaces" debate, programmers care deeply about how their code is formatted. Individuals and teams adopt various standards for line breaks and indenting, and these are usually particular to the programming language. In general, SAS doesn't care how your code is laid out -- statements are delimited by semicolons, and that's the only cue that SAS needs. However, your teammates (and your future YOU, rereading your code) might appreciate something a little more readable.

Press Ctrl+I to format your entire program, applying some reasonable readability rules to indent code lines with conditionals and looping logic. Or select just a portion of the program and press Ctrl+I to affect a smaller part of the program. You can adjust some of the formatting rules by visiting Program → Editor Options, the Indenter tab.

autoformat code

5. Zoom out for the big picture

Some SAS programs are long -- hundreds (or thousands!) of lines of code. Sometimes it's helpful to get a birds-eye view of your code to understand its structure and to help you navigate. The Zoom feature is super helpful for this. Simply press Ctrl+- (control-minus) until you get the view you need. Press Ctrl++ (control-plus) to zoom back in, or press Ctrl+0 to get to the 100% view.

This trick works for SAS logs as well, and also data sets and ODS output (including text listing, which uses the program editor in a special mode for viewing SAS output).

zoom out

6. Change the program editor font

Want to waste an afternoon? Search the Internet for "best font for programmers" and experiment with all of the results that you find. I discovered Consolas (built into Microsoft Windows) a decade ago, and I've yet to find anything better. I use it for all of my "fixed font" needs: programming, terminal windows, command consoles, etc. But you can choose your own favorite -- just don't feel that you're stuck with the default "Courier" that seems to be standard issue.

Change your font in Programs → Editor Options, Appearance tab. You'll find lots of elements that you can tweak for typeface, size and color.

7. Select columns of content with block selection

Even though column block selection -- also known as "Alt+Select" -- is a standard feature in most advanced text editors, many programmers don't know about it. It's the perfect trick for selecting just a few columns of your text without including the content that's on the rest of the line. In SAS programming, this can be handy for selecting columns of values from the text listing output and pasting somewhere else, such as into a DATALINES block. It takes a little practice to master the Alt+Select, but once you do you'll find all sorts of uses for it. To get started, simply hold down the Alt key and click-drag to highlight a vertical column of text within the editor.

column selection animation

8. Find (and replace) using regular expressions

Regular expressions are a powerful, if confusing, method for finding and replacing text that matches certain patterns. The Find/Find and Replace window in SAS Enterprise Guide supports "Regular expression search" as a checkbox option.

Here's an example. Suppose I wanted to find all occurrences of 3 numbers after the thousands separator (comma) at the end of each data line -- and I wanted to turn those digits into zeros. (I don't know why--but just stick with me here.) A regex pattern to match this is ",\d\d\d\n" (comma, followed by 3 occurrences of numeric digits, followed by a line ending). Here's an animation of this in action.

regex replace animation

For more, select Help→SAS Enterprise Guide help and search for "regular expressions". The help topics contain several examples of useful patterns.

9. Scroll just part of your document using a split view

Do you find yourself scrolling back and forth in your program view? Trying to remember what was in that DATA step at the top of your program so you can reference the proper variable in another part of your code? Instead of dealing with "scrolling whiplash", you can split the program editor view to keep one part of your code always visible while you work on another code segment that's hundreds of lines away from it.

split view

There are several ways to split your view of SAS code, log output, and listing. Check out the article here for details.

10. Break out to your other favorite editor

Please don't tell anyone, but I have a secret: SAS Enterprise Guide is not my default application associated with .SAS files. When I double-click on a .SAS file in Windows Explorer, I like to use Notepad++ to provide a quick view of what's in that program file. Don't get me wrong: I use SAS Enterprise Guide for all of my serious SAS programming work. With syntax suggestions, color coding, built-in DATA step debugger, and more -- there just isn't a better, more full-featured environment. (No, I'm not trying to troll you, diehard SAS display manager users -- you keep using what makes you happy.) But Notepad++ has a deep set of text editing features, and sometimes I like to use it for hardcore find/replace functions, deeper inspection of special characters in my files, and more.

You can launch your program into your other favorite editor from SAS Enterprise Guide. Simply right-click on the program node in your process flow, select Open → Open <program name> with Windows Default. And make sure your other editor is registered in Windows as the default "Open with" action for SAS programs. Note: this trick works only with SAS programs that you've saved locally on your Windows file system.

Open with default

More than editing -- this is your workbench

The program editor isn't just about "editing programs." It's also the launchpad for several other programmer-centric features, such as debugging your DATA step, comparing your SAS programs, viewing program history and source control, and more. If you use SAS Enterprise Guide, take the time to learn about all of its programming features -- you'll become a more productive programmer as a result.

The post Ten SAS Enterprise Guide program editor tricks appeared first on The SAS Dummy.

6月 272017
 

In a previous blog, I demonstrated a program and macro that could identify all numeric variables set to a specific value, such as 999. This blog discusses an immensely useful technique that allows you to perform an operation on all numeric or all character variables in a SAS data set. [...]

The post How to perform an operation on all numeric or all character variables in a SAS data set appeared first on SAS Learning Post.

6月 272017
 

When I teach my Data Cleaning course, the last topic I cover in the two-day course is SAS Integrity Constraints.  I find that most of the students, who are usually quite advanced programmers, have never heard of Integrity Constraints (abbreviated ICs).  I decided a short discussion on this topic would [...]

The post Keeping your data set clean: Integrity constraints appeared first on SAS Learning Post.

6月 062017
 

Wait!  Don't close this window.  I understand that regular expressions can be very complicated (yes, there are many books on the subject), but some basic expressions to test patterns such as zip codes or telephone numbers are not that difficult.  In addition, you can sometimes use Google to search for [...]

The post Using regular expressions to verify the pattern of character data appeared first on SAS Learning Post.

5月 312017
 

SAS programs in Excel, finally!When SAS Add-In for Microsoft Office was first created over a decade ago, SAS programmers were told to check their skills at the door. This new product was for non-programmers only. SAS programmers were invited to contribute to the experience by packaging their code in SAS stored processes, which end users would then run using point-and-click menus. But there was no way to write and run your SAS programs directly in Microsoft Excel or Word or PowerPoint.

This was a comfort to many SAS administrators, who wanted to provide SAS analytics to their end users but didn't want them to have to learn to program. Or perhaps to even allow them to program. But, times have changed. Citizen data scientists have been practicing their coding skills, and now they want to mix it up in Microsoft Office. In response to this demand, SAS R&D has added the SAS programming environment -- the parts that make sense, anyway -- into SAS Add-In for Microsoft Office. You can write programs, run them, and drop the results into any part of your Microsoft Office document.

Here's a short screencast of how it works in Microsoft Excel:

SAS programming in AMO

If you want to learn more and see a narrated demo from the principal developer, watch this interview with Tim Beese from SAS R&D. Tim shows the coding feature along with several other cool enhancements to SAS Add-In for Microsoft Office. As Tim explains, SAS administrators still have the final say when it comes to allowing Excel users to let loose with SAS code -- they can enable the feature by role, and so grant this as a privilege at their discretion.

The first few minutes of this video shows some impressive integration with SAS Visual Analytics and Microsoft Excel. The SAS programming demo begins around the 5-minute mark.

The post Create and run SAS code in SAS Add-In for Microsoft Office appeared first on The SAS Dummy.

5月 312017
 

SAS programs in Excel, finally!When SAS Add-In for Microsoft Office was first created over a decade ago, SAS programmers were told to check their skills at the door. This new product was for non-programmers only. SAS programmers were invited to contribute to the experience by packaging their code in SAS stored processes, which end users would then run using point-and-click menus. But there was no way to write and run your SAS programs directly in Microsoft Excel or Word or PowerPoint.

This was a comfort to many SAS administrators, who wanted to provide SAS analytics to their end users but didn't want them to have to learn to program. Or perhaps to even allow them to program. But, times have changed. Citizen data scientists have been practicing their coding skills, and now they want to mix it up in Microsoft Office. In response to this demand, SAS R&D has added the SAS programming environment -- the parts that make sense, anyway -- into SAS Add-In for Microsoft Office. You can write programs, run them, and drop the results into any part of your Microsoft Office document.

Here's a short screencast of how it works in Microsoft Excel:

SAS programming in AMO

If you want to learn more and see a narrated demo from the principal developer, watch this interview with Tim Beese from SAS R&D. Tim shows the coding feature along with several other cool enhancements to SAS Add-In for Microsoft Office. As Tim explains, SAS administrators still have the final say when it comes to allowing Excel users to let loose with SAS code -- they can enable the feature by role, and so grant this as a privilege at their discretion.

The first few minutes of this video shows some impressive integration with SAS Visual Analytics and Microsoft Excel. The SAS programming demo begins around the 5-minute mark.

The post Create and run SAS code in SAS Add-In for Microsoft Office appeared first on The SAS Dummy.

5月 262017
 

Elizabeth is courageous. Scoliosis since birth, corrective spinal surgery replaced her spine with steel, tripping on stairs permanently broke her right ankle. Then she decided to come take yoga with me. To help ease back pain & reduce hip stress, I offered options like bent legs not cross. In class [...]

The post Take that SAS option appeared first on SAS Learning Post.

5月 222017
 

In last week's article about the Flint water crisis, I computed the 90th percentile of a small data set. Although I didn't mention it, the value that I reported is different from the the 90th percentile that is reported in Significance magazine.

That is not unusual. The data only had 20 unique values, and there are many different formulas that you can use to compute sample percentiles (generally called quantiles). Because different software packages use different default formulas for sample quantiles, it is not uncommon for researchers to report different quantiles for small data sets. This article discusses the five percentile definitions that are supported in SAS software.

You might wonder why there are multiple definitions. Recall that a sample quantile is an estimate of a population quantile. Statisticians have proposed many quantile estimators, some of which are based on the empirical cumulative distribution (ECDF) of the sample, which approximates the cumulative distribution function (CDF) for the population. The ECDF is a step function that has a jump discontinuity at each unique data value. Consequently, the inverse ECDF does not exist and the quantiles are not uniquely defined.

Definitions of sample quantiles

In SAS, you can use the PCTLDEF= option in PROC UNIVARIATE or the QNTLDEF= option in other procedures to control the method used to estimate quantiles. A sample quantile does not have to be an observed data value because you are trying to estimate an unknown population parameter.

For convenience, assume that the sample data are listed in sorted order. In high school, you probably learned that if a sorted sample has an even number of observations, then the median value is the average of the middle observations. The default quantile definition in SAS (QNTLDEF=5) extends this familiar rule to other quantiles. Specifically, if the sample size is N and you ask for the q_th quantile, then when Nq is an integer, the quantile is the data value x[Nq]. However, when Nq is not an integer, then the quantile is defined (somewhat arbitrarily) as the average of the two data x[j] and x[j+1], where j = floor(Nq). For example, if N=10 and you want the q=0.17 quantile, then Nq=1.7, so j=1 and the 17th percentile is reported as the midpoint between the ordered values x[1] and x[2].

Averaging is not the only choices you can make when Nq is not an integer. The other percentile definitions correspond to making different choices. For example, you could round Nq down (QNTLDEF=3), or you could round it to the nearest integer (QNTLDEF=2). Or you could use linear interpolation (QNTLDEF=1 and QNTLDEF=4) between the data values whose (sorted) indices are closest to Nq. In the example where N=10 and q=0.17, the QNTLDEF=1 interpolated quantile is 0.3 x[1] + 0.7 x[2].

Visualizing the definitions for quantiles

The SAS documentation contains the formulas used for the five percentile definitions, but sometimes a visual comparison is easier than slogging through mathematical equations. The differences between the definitions are most apparent on small data sets that contain integer values, so let's create a tiny data set and apply the five definitions to it. The following example has 10 observations and six unique values.

data Q;
input x @@;
datalines;
0 1 1 1 2 2 2 4 5 8
;
ECDF of a small data set

You can use PROC UNIVARIATE or other methods to plot the empirical cumulative proportions, as shown. Because the ECDF is a step function, most cumulative proportions values (such as 0.45) are "in a gap." By this I mean that there is no observation t in the data for which the cumulative proportion P(X ≤ t) equals 0.45. Depending on how you define the sample quantiles, the 0.45 quantile might be reported as 1, 1.5, 1.95, or 2.

Since the default definition is QNTLDEF=5, let's visualize the sample quantiles for that definition. You can use the PCTPTS= option on the OUTPUT statement in PROC UNIVARIATE to declare the percentiles that you want to compute. Equivalently, you can use the QNTL function in PROC IML, as below. Regardless, you can ask SAS to find the quantiles for a set of probabilities on a fine grid of points such as {0.001, 0.002, ..., 0.998, 0.999}. You can the graph of the probabilities versus the quantiles to visualize how the percentile definition computes quantiles for the sample data.

proc iml;
use Q; read all var "x"; close;       /* read data */
prob = T(1:999) / 1000;               /* fine grid of prob values */
call qntl(quantile, x, prob, 5);      /* use method=5 definition  */
create Pctls var {"quantile" "prob" "x"}; append; close;
quit;
 
title "Sample Percentiles";
title2 "QNTLDEF = 5";
proc sgplot data=Pctls noautolegend;
   scatter x=quantile y=prob / markerattrs=(size=5 symbol=CircleFilled);
   fringe x / lineattrs=GraphData2(thickness=3);
   xaxis display=(nolabel) values=(0 to 8);
   yaxis offsetmin=0.05 grid values=(0 to 1 by 0.1) label="Cumulative Proportions";
   refline 0 1 / axis=y;
run;
Sample quantiles (percentiles) for a small data set

For each probability value (Y axis), the graph shows the corresponding sample quantile (X axis) for the default definition in SAS, which is QNTLDEF=5. The X axis also displays red tick marks at the location of the data. You can use this graph to find any quantile. For example, to find the 0.45 quantile, you start at 0.45 on the Y axis, move to the right until you hit a blue marker, and then drop down to the X axis to discover that the 0.45 quantile estimate is 2.

If you prefer to think of the quantiles (the X values) as a function of the probabilities, just interchange the X= and Y= arguments in the SCATTER statement (or turn your head sideways!). Then the quantile function is a step function.

Comparing all five SAS percentile definitions

It is easy to put a loop around the SAS/IML computation to compute the sample quantiles for the five different definitions that are supported in SAS. The following SAS/IML program writes a data set that contains the sample quantiles. You can use the WHERE statement in PROC PRINT to compare the same quantile across the different definitions. For example, the following displays the 0.45 quantile (45th percentile) for the five definitions:

/* Compare all SAS methods */
proc iml;
use Q; read all var "x"; close;       /* read data */
prob = T(1:999) / 1000;               /* fine grid of prob values */
create Pctls var {"Qntldef" "quantile" "prob" "x"};
do def = 1 to 5;
   call qntl(quantile, x, prob, def); /* qntldef=1,2,3,4,5 */
   Qntldef = j(nrow(prob), 1, def);   /* ID variable */
   append;
end;
close;
quit;
 
proc print data=Pctls noobs;
   where prob = 0.45;                 /* compare 0.45 quantile for different definitions */
   var Qntldef quantile;
run;

You can see that the different definitions lead to different sample quantiles. How do the quantile functions compare? Let's plot them and see:

ods graphics / antialiasmax=10000;
title "Sample Percentiles in SAS";
proc sgpanel data=Pctls noautolegend;
   panelby Qntldef / onepanel rows=2;
   scatter x=quantile y=prob/ markerattrs=(size=3 symbol=CircleFilled);
   fringe x;
   rowaxis offsetmax=0 offsetmin=0.05 grid values=(0 to 1 by 0.1) label="Cumulative Proportion";
   refline 0 1 / axis=y;
   colaxis display=(nolabel);
run;
Compare percentile definitions in SAS

The graphs (click to enlarge) show that QNTLDEF=1 and QNTLDEF=4 are piecewise-linear interpolation methods, whereas QNTLDEF=2, 3, and 5 are discrete rounding methods. The default method (QNTLDEF=5) is similar to QNTLDEF=2 except for certain averaged values. For the discrete definitions, SAS returns either a data value or the average of adjacent data values. The interpolation methods do not have that property: the methods will return quantile values that can be any value between observed data values.

If you have a small data set, as in this blog post, it is easy to see how the percentile definitions are different. For larger data sets (say, 100 or more unique values), the five quantile functions look quite similar.

The differences between definitions are most apparent when there are large gaps between adjacent data values. For example, the sample data has a large gap between the ninth and tenth observations, which have the values 5 and 8, respectively. If you compute the 0.901 quantile, you will discover that the "round down" method (QNTLDEF=2) gives 5 as the sample quantile, whereas the "round up" method (QNTLDEF=3) gives the value 8. Similarly, the "backward interpolation method" (QNTLDEF=1) gives 5.03, whereas the "forward interpolation method" (QNTLDEF=4) gives 7.733.

In summary, this article shows how the (somewhat obscure) QNTLDEF= option results in different quantile estimates. Most people just accept the default definition (QNTLDEF=5), but if you are looking for a method that interpolates between data values, rather than a method that rounds and averages, I recommend QNTLDEF=1, which performs linear interpolations of the ECDF. The differences between the definitions are most apparent for small samples and when there are large gaps between adjacent data values.

Reference

For more information about sample quantiles, including a mathematical discussion of the various formulas, see
Hyndman, R. J. and Fan, Y. (1996) "Sample quantiles in statistical packages", American Statistician, 50, 361–365.

The post Quantile definitions in SAS appeared first on The DO Loop.