SAS tips

8月 292018

The concept of "current working directory" is important within any SAS program that reads or creates external files. In SAS, when you reference a file location with a relative path (for example, "./projects/mydata.pdf"), that file reference resolves to an absolute path by way of the working directory. You can control the initial working directory by modifying the shell scripts that launch the SAS process, or by specifying the simple SAS macro that allows you to learn the current working directory. The macro uses a trick to assign a SAS fileref to the current path ('.'), grab the full path of that fileref by using Read the article for the full source (it's only about 7 lines). Here's how you would use it:

56         %put Current path is %curdir;
Current path is C:\WINDOWS\system32

As you might infer from my example here, I'm running this on a managed Windows environment. Most users cannot write to the "C:\WINDOWS\system32" path (and would not want to), so any relative file paths in my SAS code would cause errors. Maybe you've seen something like this:

25         ods html file="./test.html";
NOTE: Writing HTML Body file: ./test.html
ERROR: Insufficient authorization to access C:\WINDOWS\system32\test.html.
ERROR: No body file. HTML output will not be created.

If I want to use a relative path, I need to change the current working directory. Fortunately, there's a simple way to do that.

Change the current directory in SAS

Use the

/* working path for my projects */
%let rc = %sysfunc(dlgcdir('u:/projects'));
ods html file="./test.html";
proc print data=sashelp.class; run;
ods html close;

I can use my account-specific environment variables to make these paths work for all users. For example, on Windows I can reference the USERPROFILE environment variable. (On Unix, I can use the HOME environment variable instead.)

/* working path for my projects */
%let user = %sysget(USERPROFILE);
%let rc = %sysfunc(dlgcdir("&user./Documents"));
/* create an output data folder if needed */
options dlcreatedir;
libname outdata "./data";
ods html file="./test.html";
data outdata.class;
 set sashelp.class;
proc print data=outdata.class; run;
ods html close;

Here's my log output. Notice how the HTML file and the output data folder are both created at locations relative to my home directory.

25         /* working path for my projects */
26         %let user = %sysget(USERPROFILE);
27         %let rc = %sysfunc(dlgcdir("&user./Documents"));
NOTE: The current working directory is now "C:\Users\sascrh\Documents".
29         options dlcreatedir;
30         libname outdata "./data";
NOTE: Library OUTDATA was created.
NOTE: Libref OUTDATA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: C:\Users\sascrh\Documents\data
32         ods html file="./test.html";
NOTE: Writing HTML Body file: ./test.html
33         data outdata.class;
34          set sashelp.class;
35         run;

If using SAS Enterprise Guide, you can add DLGCDIR function steps to the startup statements that run when you connect to SAS, ensuring that your working directory starts in a valid location for SAS output. You can specify those statements in Tools->Options->SAS Programs, "Submit SAS code when server is connected." A SAS administrator can also add code to the AUTOEXEC file that runs when the SAS session begins, thus helping to manage this for larger groups of SAS users.

See also

The post Manage the current directory within your SAS program appeared first on The SAS Dummy.

7月 062018

SAS programmers have long wanted the ability to control the flow of their SAS programs without having to resort to complex SAS macro programming. With SAS 9.4 Maintenance 5, it's now supported! You can now recently came to light on SAS Support Communities. (Thanks to Super User Tom for asking about it.)

Prior to this change, if you wanted to check a condition -- say, whether a data set exists -- before running a PROC, you had to code it within a macro routine. It would look something like this:

/* capture conditional logic in macro */
%macro PrintIfExists();
 %if %sysfunc(exist(work.result)) %then
    proc means data=work.result;
    %PUT WARNING: Missing WORK.RESULT - report process skipped.;
/* call the macro */

Now you can simplify this code to remove the %MACRO/%MEND wrapper and the macro call:

/* If a file exists, take an action */
/* else fail gracefully */
%if %sysfunc(exist(work.result)) %then
    proc means data=work.result;
    %PUT WARNING: Missing WORK.RESULT - report process skipped.;

Here are some additional ideas for how to use this feature. I'm sure you'll be able to think of many more!

Run "debug-level" code only when in debug mode

When developing your code, it's now easier to leave debugging statements in and turn them on with a simple flag.

/* Conditionally produce debugging information */
%let _DEBUG = 0; /* set to 1 for debugging */
%if &_DEBUG. %then
    proc print data=sashelp.class(obs=10);

If you have code that's under construction and should never be run while you work on other parts of your program, you can now "IF 0" out the entire block. As a longtime C and C++ programmer, this reminds me of the "#if 0 / #endif" preprocessor directives as an alternative for commenting out blocks of code. Glad to see this in SAS!

/* skip processing of blocks of code */
/* like #if 0 / #endif in C/C++      */
%if 0 %then
    proc ToBeDetermined;
      READMYMIND = Yes;

Run code only on a certain day of the week

I have batch jobs that run daily, but that send e-mail to people only one day per week. Now this is easier to express inline with conditional logic.

/*If it's Monday, send a weekly report by email */
%if %sysfunc(today(),weekday1.)=2 %then
    options emailsys=smtp;
    filename output email
      subject = "Weekly report for &SYSDATE."
      from = "SAS Dummy <>"
      to = ""
      ct ='text/html';
  ods tagsets.msoffice2k(id=email) 
    file=OUTPUT(title="Important Report!")
   title "The Weekly Buzz";
   proc print;
  ods tagsets.msoffice2k(id=email) close;

Check a system environment variable before running code

For batch jobs especially, system environment variables can be a rich source of information about the conditions under which your code is running. You can glean user ID information, path settings, network settings, and so much more. If your SAS program needs to pick up cues from the running environment, this is a useful method to accomplish that.

/* Check for system environment vars before running code */
%if %sysfunc(sysexist(ORACLE_HOME)) %then
    %put NOTE: ORACLE client is installed.;
    /* assign an Oracle library */
    libname ora oracle path=corp schema=alldata authdomain=oracle;

Limitations of %IF/%THEN in open code

As awesome as this feature is, there are a few rules that apply to the use of the construct in open code. These are different from what's allowed within a %MACRO wrapper.

First rule: your %IF/%THEN must be followed by a %DO/%END block for the statements that you want to conditionally execute. The same is true for any statements that follow the optional %ELSE branch of the condition.

And second: no nesting of multiple %IF/%THEN constructs in open code. If you need that flexibility, you can do that within a %MACRO wrapper instead.

And remember, this works only in SAS 9.4 Maintenance 5 and later. That includes the most recent release of SAS University Edition, so if you don't have the latest SAS release in your workplace, this gives you a way to kick the tires on this feature if you can't wait to try it.

The post Using %IF-%THEN-%ELSE in SAS programs appeared first on The SAS Dummy.

4月 112017

With the advent of things like car GPS & Google Maps, and a steady supply of nice maps from certain news sources (such as the New York Times), people have finally embraced the idea that mapping data can be very useful. And if you are into data visualization, you have [...]

The post Your mapping toolkit Tip #1 - reducing border complexity appeared first on SAS Learning Post.

6月 242016

One thing that we have a lot of at SAS: installations of SAS software that we can run. I have SAS for Windows on my laptop, and I have access to many centralized instances of SAS that run on Linux and Windows servers. (I also have access to mainframe SAS, though it's been a while since I've used it. When I log in, I picture a Rube-Goldberg style mechanism that pokes an intern to mount a tape so my profile can be reloaded.)

I often develop programs using my local instance of SAS and SAS Enterprise Guide, but deploy them for use on a central server. I might run them as batch jobs or interactively with SAS Enterprise Guide or SAS Studio or even in SAS/IntrNet.

Our IT department wants SAS employees to have seamless access to their files whether on Windows or on Unix-style file systems, and so they make it easy to access the same network path from Windows (using UNC notation, or "\serverpath" syntax) and Unix (using "/node/usr/path" syntax). As I develop my SAS programs, I want the programs to work the same whether run from Windows or Unix, and I don't want to have to change LIBNAME paths each time. Fortunately, SAS programs are usually portable across different operating systems, and while SAS data sets might have different encodings across systems, SAS can always read a data set that was created by a different version.

I have a simple technique that references the proper path for the operating system that I'm using. I build a SAS macro variable by using the IFC function and the &SYSSCP automatic variable to check whether I'm running on Windows, then assign the path accordingly.

/* Use the IFC function as a shorthand for if-then, returning a character string */
%let tgtpath = %sysfunc(
  ifc(&SYSSCP. = WIN,
libname tgt "&tgtpath.";

When I run this on SAS for Linux, I see this in the log:

NOTE: Libref TGT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /r/node/vol/vol01/mydept/project

And on Windows:

NOTE: Libref TGT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: \sasprodrootdeptmydeptproject
tags: SAS programming, SAS tips

The post Assign a SAS library to a different path depending on your OS appeared first on The SAS Dummy.

7月 172014

When it comes to e-mail-based newsletters, I'm of two minds. On one hand, I feel like I receive enough (or maybe too much) e-mail and I'm reluctant to clog up my inbox with more stuff -- especially if it's information that's located elsewhere (such as on that big backup drive that we call "the Internet").

tipsextra_conceptBut on the other hand, I don't usually have time to go searching the Web for stuff that might someday be useful to me, or even to pay regular visits to the sources that I know will have good information (such as blogs and discussion forums). If someone collected some of the best tidbits from these sources and delivered them to me -- even in e-mail -- I think that would be useful. Do you agree?

We know from experience that SAS users love to see tips about how to do more with SAS. So we got to thinking: What if we curated an e-mail newsletter that was ALL SAS tips? Where all of the content was coming from the "hey, we bet you didn't know this" category?

We're making that happen, beginning this month. It's called SAS Tech Report: Tips Extra. It contains tips -- some from SAS staff and some from the larger SAS community -- to save you time and expand your mind. If you subscribe to the SAS Tech Report newsletter already, then do nothing -- you'll receive it automatically. If you don't yet subscribe, then visit the e-Newsletters section of and click the link to sign up. It's free, of course -- all you need is a SAS profile (which you probably already have if you participate in or interact with SAS Technical Support).

I hope that you like the content, and that you appreciate it showing up in your inbox. And if you have feedback or ideas for topics to feature, let me know here in the comments.

tags: SAS support, SAS tips
1月 072014

Because I began my SAS career in the Publications division, I like to think that I have a keen eye when it comes to SAS documentation. When I first visited the SAS 9.4 online documentation, I immediately noticed that it had a different look. Examine the image below; can you see what I mean? (Click on the image to see a larger version.)

Aside from a few layout enhancements and more helpful details, I noticed the use of a different typeface for the text. It's a font that I didn't recognize, and I wondered how it had been installed on my system.

It turns out that this font face (named "Lato") is not installed on my system; it's a web font. A web font is downloaded and rendered "on the fly" by your web browser. This gives the web designer more control over the exact appearance of the text, even across different operating systems and devices that share only a small subset of generic font styles.

There are different sources for web fonts. Some are free to use, while others require a nominal licensing fee. If you have a particular typeface that is important to your company brand, it might be worth a licensing fee to ensure that this typeface is used consistently in all of your web content. However, the SAS documentation (and many other sites) use the free Google Fonts.

After I learned all of this I wondered: how can I use web fonts in SAS ODS output?

Specifying a web font in ODS HTML

There are three ways to "import" a web font in your HTML content:

  1. using a <link> tag to reference a directive from an external style
  2. using an @import directive from within a CSS file or <style> tag
  3. using JavaScript to dynamically insert a web font style reference into the page.

The Google Font web site provides code snippets for each of these in HTML.

Once you import the font, you must then reference the font name in CSS "font-family" style attributes for the different element classes that you want to affect. For example, if you want ODS tabular data to use the Lato font, you must change the "data" class to include it:

.data {
  font-family: 'Lato', sans-serif;

I decided that the <link>-tag approach was the simplest method to import the web font. I copied the <link>-tag directives from the Google Fonts entry for Lato and Droid Sans, and then "injected" them into the ODS HTML output by using the HEADTEXT option. Then I used PROC TEMPLATE to modify the style attributes for specific ODS-related style classes; these attributes will translate into CSS when SAS creates your HTML. Here's the program:

/* These snippets copied from facility    */
/* Macro for HEADTEXT option, since the value cannot exceed 256 chars */
%macro ods_html_webfont;
<link href=',700' 
  rel='stylesheet' type='text/css'>
<link href=',700' 
  rel='stylesheet' type='text/css'>
proc template;
  define style webfont;
    /* for this example, inherit existing style elements from HTMLBLUE */
  style header from header /
    fontfamily="'Droid Sans', sans-serif";
  style data, body from _self_ /
    fontfamily="'Lato', sans-serif";
  class titlesandfooters /
    fontfamily="'Lato', sans-serif";
ods html (id=wf)
   file="c:\temp\wf_test.html"(title="Web Fonts Test") 
title "A new look for my report";
proc means;
ods html (id=wf) close;

Here's the result as seen in my Chrome browser:

(Want to compare this to a version that doesn't use web fonts?)

Limitations of web fonts

Before you consider using web fonts in all of your SAS-generated content, there are a few restrictions that you should review:

  • Web fonts can be used only in HTML output -- output that you intend to display in a web browser. The browser will download and render the font based on CSS or JavaScript directives. This means that this technique won't work for RTF or PDF output.
  • Web fonts can apply to textual content only, and not to images that are generated by SAS graphical procedures. SAS graphical output is usually rendered into an image file (such as a PNG file) within your SAS session. The appearance is controlled by SAS styles that are defined in your SAS session, and any referenced fonts must be accessible to SAS.
  • Because web fonts must be downloaded by the browser as the HTML page loads, this can have an impact on how quickly the page is rendered. Each Google web font provides some guidance about this potential impact. For the best response, include references to the minimum number of typefaces that you need for the content.
  • And of course, for a web font to download you must be connected to the Internet. It's a good idea to always specify a fallback font family (ex: sans-serif) in your styles so that even if your web font can't load, your style still provides some cue for how to render the text.
tags: CSS, ods, PROC TEMPLATE, SAS 9.4, SAS tips, web fonts
7月 032013

When I work on SAS projects that create lots of files as results, it's often a requirement that those files be organized in a certain folder structure. The exact structure depends on the project, but here's an example:

   |__ html
       |__ images
   |__ xls
   |__ data

Before you can have SAS populate these file folders, the folders have to actually exist. Traditionally, SAS programmers have handled this by doing one of the following:

  • Simply require that the folders exist before you run through the project. (This is the SEP method: Somebody Else's Problem.)
  • Use SAS statements and shell commands (via SYSTASK or other method) to create the folders as needed. The SAS-related archives are full of examples of this. It can get complex when you have to account for operating system differences, and whether operating system commands are even permitted (NOXCMD system option).

In SAS 9.3 there is a new system option that simplifies this: DLCREATEDIR. When this option is in effect, a LIBNAME statement that points to a non-existent folder will take matters into its own hands and create that folder.

Here's a simple example, along with the log messages:

options dlcreatedir;
libname newdir "/u/sascrh/brand_new_folder";

NOTE: Library NEWDIR was created.
NOTE: Libref NEWDIR was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /u/sascrh/brand_new_folder

You might be thinking, "Hey, SAS libraries are for data, not for other junk like ODS results." Listen: we've just tricked the LIBNAME statement into making a folder for you -- you can use it for whatever you want. I won't tell.

In order to create a series of nested folders, you'll have to create each folder level in top-down order. For example, if you need a "results" and a "results/images" folder, you can do this:

%let outdir=%sysfunc(getoption(work));
/* create a results folder in the WORK area, with images subfolder */
options dlcreatedir;
libname res "&outdir./results";
libname img "&outdir./results/images";
/* clear the librefs - don't need them */
libname res clear;
libname img clear;

Or (and this is a neat trick) you can use a single concatenated LIBNAME statement to do the job:

libname res ("&outdir./results", "&outdir./results/images");
libname res clear;

NOTE: Libref RES was successfully assigned as follows: 
      Levels:           2
      Engine(1):        V9 
      Physical Name(1): /saswork/SAS_workC1960000554D_gsf0/results
      Engine(2):        V9 
      Physical Name(2): /saswork/SAS_workC1960000554D_gsf0/results/images

If you feel that folder creation is best left to the card-carrying professionals, don't worry! It is possible for a SAS admin to restrict use of the DLCREATEDIR option. That means that an admin can set the option (perhaps to NODLCREATEDIR to prohibit willy-nilly folder-making) and prevent end users from changing it. Just let them try, and they'll see:

13         options dlcreatedir;
WARNING 36-12: SAS option DLCREATEDIR is restricted by your Site 
Administrator and cannot be updated.

That's right -- DENIED! Mordac the Preventer would be proud. Job security: achieved!

Exact documentation for how to establish Restricted Options can be a challenge to find. You'll find it within the Configuration Guides for each platform in the Install Center. Here are quick links for SAS 9.3: Windows x64, Windows x86, and UNIX.

tags: DLCREATEDIR, Restricted Options, sas administration, SAS libraries, SAS programming, SAS tips
10月 032012

As citizens of the Internet, we are all familiar with IP addresses -- probably more so than our Internet founding fathers had ever intended. These addresses are typically represented in a 4-piece segmented list of numbers separated by dots. Here is an example: "".

Each segment is called an octet because it contains 8 (count 'em, eight!) bits. The four-octect IP address is part of the IPv4 standard.

Note: There is a newer IPv6 standard (featuring 16 octets) that many newer networks use and which allows for more addresses. This has become necessary because all new consumer products are required by law to connect to the Internet. (I think that each of my daughter's "Polly Pocket" pieces can connect to WiFi.) But in this article I'm ignoring IPv6.

UPDATE 2020-Sep-04: My colleague Agata Bogacki has written a newer article that includes IPv6 and IPv4 and techniques to map to a geolocation.

The easy-to-read segmented IP address is actually a 32-bit number, and sometimes it is useful to convert the display value into its numeric form. For example, consider the databases that help you to map an IP address to a geographic location in the world. These databases use a numerical range to map an address to a country or city. (For more on range-based geocoding, see this topic in the PROC GEOCODE documentation.)

SAS code for converting IPv4 values to numbers

Here is a SAS program that converts a character-based, 4-segment IP address into its equivalent numeric value. It uses the SCAN function, a DATA step ARRAY, and a little bit of math to do the work:

/* Calculate the numerical IP from "segmented" IP address            */
/* Example: (from Right to Left)                                     */
/* = 4 + (3 * 256) + (2 * 256 * 256) + (1 * 256 * 256 * 256) */
/*   is 4 + 768 + 13,1072 + 16,777,216 = 16,909,060                  */
data ip_numbers (keep=ip_address ip_numeric);
  infile datalines dsd;
  length ip_address $ 20 ip_numeric 8;
  input ip_address;
  array ip_part {4};
  do i = 1 to 4;
    ip_part{i} = scan(ip_address,i,'.');
  ip_numeric = ip_part{4} +
    (ip_part{3} * 256) +
    (ip_part{2} * 256 * 256) +
    (ip_part{1} * 256 * 256 * 256);

Here's the output:

Mapping IP address to a geo location

With this mapping, I can then combine my collection of IP addresses with one of the IP-to-geolocation databases that are available. (SAS provides a macro to help work with MaxMind, which you can learn about in this topic.) Here's a sample result:

The post Using SAS to convert IP addresses into numerical IP values appeared first on The SAS Dummy.

8月 152012

The project that I'm currently working on requires several input data tables, and those tables must have a specific schema. That is, each input table must contain columns of a specific name, type, and length in order for the rest of the system to function correctly. The schema requirements aren't too fancy; there are no specs for keys, indexes, or constraints. It's simply "your data sets need columns that look like this".

Even though the schema is "published", the data tables themselves will be prepared by humans using Who Knows What Processes. Therefore, we must adopt a "trust but verify" approach before accepting the data and turning the crank on what could be a long-running analysis.

I could "eyeball" the differences by capturing the data column attributes from each table and comparing them with what I know I need, but that approach doesn't scale well.

I wrote a simple SAS macro that I use to create a report that shows whether the input data passes a simple schema "sniff test". It compares a "candidate" table against a table that has a known "good" column layout, and reports on the substantive differences.

DOWNLOAD: %check_reference_data from here.

The output of the macro is another data set that contains one record per column-pair, showing a list of potential problems with the input data, ranked by severity. The severity assignments are a bit arbitrary, but effective enough for my project:

  • "SEV 1" is assigned if a required column is missing, or is present but has a different type (numeric vs. character).
  • "SEV-2" is assigned if a required column has a greater-than-expected length, as it might lead to truncation of data values downstream.
  • "SEV-3" is assigned if the column has a different SAS format, as it could indicate possible misinterpretation (a DATE format vs. DATETIME, for example).
  • And "SEV-4" is more of an "FYI" when the input data contains additional columns that aren't required.

Sample uses:

title "Does CLASSFIT have everything that CLASS has?  Yes, and more";
proc print data=work.check;

title "Does CLASS have everything that CLASSFIT has?  No, it does not.";
proc print data=work.check;

If you examine the code behind the macro, you might notice that it creates a temporary view of the input data. Why? During work on my project, I noticed that some of the supplied input data differed from the schema only by one or two column names. Sometimes it's easier to "fix" that data on the way in than to send it back to the customer to regenerate. The temp view allows me to apply simple data set options like this:

title "Fixed for customer-provided data that was SEX-less";
proc print data=work.check;

And by the way, for this check to work I don't need to keep a complete copy of "good" data lying around. I can create an empty table with the desired schema "on the fly" like so, and then use it as the reference table in my macro:

/* create reference table attributes */
/* using SQL, but DATA step works too */
proc sql;
create table work.PATIENT (
  SEX                    VARCHAR(1) FORMAT=$1.,
  YOB                    NUMERIC(8) FORMAT=4.,
  ZIP_CODE               VARCHAR(5),
  PATIENT_ID             VARCHAR(15)

This macro provides a simple, "good-enough" approach for verifying the input data. It provides instant feedback as to whether the data can be used, and gives us confidence that we can proceed to the next step of the analysis.

tags: data sets, SAS programming, SAS tips, sql
8月 152012

I'm working on a SAS programming project with a large team. Each team member is responsible for a piece of the overall system, and the "contract" for how it all fits together is The Data. For example, I've got a piece that performs some data manipulation and produces several output data sets, and my colleagues Bryan and Cindy will then pick those up as input into their pieces. In order for this puzzle to fit together cleanly, we need to agree on library names, data set names, and the column names, lengths and formats.

So the team members have been flinging PROC CONTENTS output at each other for several weeks, in an effort to make sure that this jigsaw puzzle comes together as it should. However, I know that the team is using a spreadsheet to keep track of all of these data schemas, and so the PROC CONTENTS exercise turns into an exercise of getting the output listing into Excel.

If all you want is a quick Excel-friendly listing of your data columns and their attributes, that's easy to get from SAS Enterprise Guide. Here's how:

1. Add the data set to your SAS Enterprise Guide project. (You can accomplish this from File->Open->Data, or drag the data set file onto your process flow.)

2. In the data grid view, select the Properties button from the toolbar. The Properties window appears for your data set.

3. Select the Columns tab, then click the Copy to clipboard button.

4. Paste the result into your Excel spreadsheet.

The result: a listing of your data column attributes, with each attribute in its own column. Easy!

tags: SAS Enterprise Guide, SAS tips