SAS Report: You're soaking in it

Chris Hemedinger
Chris Hemedinger|February 22, 2012

Do you want your report to look good on the web, or to look good when you print it? Pick one.

Before the SAS Report file format, that was the choice that you faced.

HTML is perfect for the web browser. It's easy to scroll through tables, apply an attractive color scheme, and interact with graphical output. But if you try to print it, it's a mess. Tables don't always fit on a page, page numbering is nonsensical, and your colors get lost.

If you want printed output, PDF is the way to go. Your output is optimized for the printed page. Tables, if they don't fit on a single page, have repeating column headers on subsequent pages. You can control whether it's landscape or portrait. But to view it on the web, you need a special browser plugin. If you try to share sections of content with copy/paste operations, you can end up with some funky results.

SAS Report combines the best of these two mainstay formats into a single destination. In my Chunky-soup-style slogan,  "SAS Report views like HTML and prints like PDF."

If you use SAS Enterprise Guide, SAS Web Report Studio, or SAS Add-In for Microsoft Office, then you already use SAS Report format. (You're soaking in it, metaphorically...though it might not result in softer hands.)

Here's a partial list of where SAS Report makes an appearance:

  • It's the default output format for results in SAS Enterprise Guide (version 4.2 and later).
  • It's the format for report definitions in SAS Web Report Studio, which are stored as SRX files (SAS Report XML).
  • It's how the SAS Add-In for Microsoft Office pulls SAS output into your Microsoft Office client (such as Excel), and keeps the substance of your result while allowing you to apply custom formatting using Office features.  How does that work?  The SAS Report format actually keeps the data (substance) separate from the appearance (layout and style), so it's easier for the Add-In to keep them straight.
  • It's an ODS destination (ODS tagsets.sasreport12) that can feed content to all of these client applications from any SAS program.

SAS Report is the lingua franca of SAS business intelligence applications.  You can share your SAS Enterprise Guide report with SAS Web Report Studio.  In the SAS Add-In for Office, you can open reports that were created in SAS Web Report Studio or SAS Enterprise Guide.

SAS Report output can also be pulled apart and combined to create entire new reports.  The Report Builder in SAS Enterprise Guide (File->New->Report) allows you to take output from different tasks and programs within your project, and combine these into a report definition that shows just the pieces you want to share.  You can even design the report with side-by-side layouts, add text and graphics, and control how the report fits on a page.

SAS Report may be a proprietary SAS file format, but that doesn't tie your content to SAS client applications. You can use SAS Enterprise Guide to save the report to PDF or HTML when you need to share with colleagues who are less fortunate (who don't have access to SAS).  And of course, you can "print" SAS Web Report Studio reports to PDF.

And here's another little trick: SAS Report doesn't just print nicely -- it also copies nicely. In SAS Enterprise Guide, you can right-click on a table or graph in the SAS Report viewer and select Copy, then Paste into a Microsoft Office document (such as a spreadsheet or Word doc). SAS Enterprise Guide puts well-formed HTML "on the clipboard", and that's readily accepted by Office applications. (Hat tip to Tricia for reminding me how cool this feature can be.)

Post a Comment

How do I export from SAS to Excel files: Let me count the ways

Chris Hemedinger
Chris Hemedinger|February 11, 2012

I have a love-hate relationship with spreadsheet data. I am interested in finding data to analyze, and if it arrives in the form of a spreadsheet, I'll take it. And I like to deliver results and reports, but often my constituents ask for it as a spreadsheet that they can then manipulate further. <Sigh.>

A spreadsheet is not a database, so it can be a challenge to whip spreadsheet data into analysis-ready shape. Likewise, saving results as a spreadsheet can lose something in the translation -- usually value formatting, appearance attributes, or graphs.

SAS offers many ways to read from and write to Microsoft Excel spreadsheets. They each have pros and cons. This post is an inventory of the methods that I know about for creating Excel files from within SAS.

Some "bits" about 32-bit and 64-bit architecture

Before I get to the Big List, let me set the stage by describing a few terms and concepts.

In order to create Excel files directly from SAS, you need SAS/ACCESS to PC Files. This product enables all sorts of file exchanges between SAS and other PC-based applications, including Microsoft Excel, Microsoft Access, SPSS, and more.

SAS/ACCESS to PC Files includes a component called the PC Files Server. The PC Files Server is a service-based application that runs apart from SAS on a Windows node. It accepts requests from SAS to convert data to and from Excel (and other file types). Traditionally, this innovation allows SAS running on UNIX (where there are no native facilities for Excel data) to exchange data with PC-based data programs.

Recent changes in desktop computing have complicated the picture. Newer desktop machines all have 64-bit architecture, and most organizations are now adopting 64-bit versions of Microsoft Windows. All of your 32-bit applications (designed for x86 architecture) still can still run, of course, but there are a few hard-and-fast rules. One of those rules is that a 64-bit application cannot dynamically load 32-bit modules in its process space. And guess what? There is a better-than-even chance that the built-in data providers that you have for Microsoft Excel -- the bits that allow SAS to write to Excel on Windows -- are 32-bit modules. This means that the PROC EXPORT DBMS=EXCEL program that worked in your 32-bit SAS session will not work in your 64-bit SAS session.

There are two remedies for this bitness mismatch. First, you could install the 64-bit data providers (which accompany the 64-bit version of Microsoft Office). But you cannot have both the 32-bit and 64-bit versions of these data providers on the same machine; if you have 32-bit Microsoft Office, then you're stuck with the 32-bit providers for now.

The second remedy is to use the PC Files Server, right there on the same Windows machine where SAS is running. This allows a 64-bit SAS process to delegate the data exchange to a 32-bit PC Files Server process. Thanks to the out-of-process communication, this circumvents the bit architecture mismatch. To make this work you don't have to set up any additional software, but your SAS programs must change to use DBMS=EXCELCS. The EXCELCS keyword tells SAS to use the PC Files Server instead of attempting to use in-process data providers.

Exporting to Excel: ways to get there from SAS

With the architecture lesson behind us, here's my list for how to put SAS content into Microsoft Excel. I won't dive into much detail about each method here; you can follow the links to find more documentation.

These methods use features of SAS/ACCESS to PC Files:

LIBNAME EXCEL – reads/writes Excel files at the sheet level when the bitness of SAS (32- or 64-bit) matches the bitness of Microsoft Office installed (or more specifically, the ACE drivers that accompany Office). An Excel file is viewed as a SAS library, while sheets/ranges are the member tables. Requires exclusive lock on an existing Excel file.

LIBNAME PCFILES – does the same as LIBNAME EXCEL, but uses PC Files Server. Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match.

PROC EXPORT DBMS=EXCELCS – uses PC Files Server to write an Excel file. There are various options to control the output behavior. Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match.

PROC EXPORT DBMS=EXCEL - writes Excel files when the bitness of SAS (32- or 64-bit) matches the bitness of Microsoft Office installed (or more specifically, the ACE drivers that accompany Office).

PROC EXPORT DBMS=XLS – writes Excel (XLS) files directly, no driver or PC Files Server needed. Has limits on volume and format. Works on Windows and UNIX.

PROC EXPORT DBMS=XLSX – new in 9.3M1, writes Excel 2010 files (XLSX format) directly. No driver or PC Files Server needed. Works on Windows and UNIX.

The following methods do not require SAS/ACCESS to PC Files, so they are popular, even if they don't produce "native" Excel files:

PROC EXPORT DBMS=CSV – produces comma separated value files, most often used in Excel.

ODS TAGSETS.CSV (or just DATA step and FILE output) – produces comma separated value files, most often used in Excel.

ODS TAGSETS.EXCELXP – uses ODS to create an Office XML file. Provides a fair amount of control over the content appearance, but recent versions of Excel do not recognize as a "native" format, so user is presented with a message to that effect when opening in Excel.

FILENAME DDE – uses Windows messages to control what goes into an Excel file, down to the cell level. Works only when SAS for Windows and Excel are on the same machine. Doesn't work in SAS workspace servers or stored process servers (often accessed with SAS Enterprise Guide). It's an antiquated approach, but offers tremendous control that many long-time SAS users enjoy.

SAS client applications make it easier

While I've focused on the SAS programming methods for creating Excel files, applications like SAS Enterprise Guide and the SAS Add-In for Microsoft Office make the operation a breeze. SAS Enterprise Guide can import and export Excel files through point-and-click methods, and SAS/ACCESS to PC Files is not needed to make that work. (However, the work is not captured in a SAS program, so it cannot be run in SAS batch jobs or stored processes.)

SAS Add-In for Microsoft Office turns the problem on its head. By allowing you to access SAS data and analytics from within Microsoft Excel, you pull the results into your Excel session, rather than export them from your SAS session.

Post a Comment

Review your metadata profiles using SAS Enterprise Guide automation

Chris Hemedinger
Chris Hemedinger|February 2, 2012

We call it the "metadata profile", but really it's like a telephone number that connects you to your SAS environment. Just as a telephone number has component parts (country code, area code, exchange), the metadata profile contains information that allow you to "dial in" to your SAS servers. This information includes:

  • a host name (node name or IP address of your SAS metadata server)
  • a port number (the TCP/IP port that the SAS metadata server uses to accept connections)
  • your credentials (the user ID and password that identify you when you connect)

All of this is captured under a friendly name, which you assign when you define the profile.  This is similar to the way that you would file a colleague's information in your Rolodex (still have that?) or contacts list.

When you're connected to a SAS environment in SAS Enterprise Guide, you'll see some of that profile information reflected in the status bar:

To activate a different profile or to define a new one, you can click on the Connections link in the status bar. This invokes the Connections window, showing all of your available profiles and their details.

The profile name is important, because you can make use of this name as a shortcut (similar to "speed dial") to direct SAS Enterprise Guide to a particular metadata environment automatically.  To learn more, read about how to use SAS Enterprise Guide with different SAS environments.

You can also use the profile name in SAS Enterprise Guide automation scripts, as shown in this example about running SAS programs in batch.

My objective in this blog post (and I'm taking a while to get to it) is to show how to use an automation script to list the available profiles that you have defined for your installation of SAS Enterprise Guide.  In this VBScript program, I use the Application.Profiles collection to obtain the details of each defined profile.  To set an active profile in script, use the Application.SetActiveProfile() method.  Note that if you want to run in "no profile" mode (not connected to metadata, simply using your local SAS installation), you specify "Null Provider" as the profile name.  (Aside: "Null Provider" would make a great name for a rock band, or maybe for a blog.)

Here's the VBScript program:

' force declaration of variables in VB Script
Option Explicit
Dim Application
' Create a new SAS Enterprise Guide automation session
Set Application = WScript.CreateObject("SASEGObjectModel.Application.4.3")
WScript.Echo Application.Name & ", Version: " & Application.Version
 
' Discover the available profiles that are defined for the current user
Dim i 
Dim oShell
Set oShell = CreateObject( "WScript.Shell" )
WScript.Echo "Metadata profiles available for " _
   & oShell.ExpandEnvironmentStrings("%UserName%")
WScript.Echo "----------------------------------------"
For i = 1 to Application.Profiles.Count-1
  WScript.Echo "Profile available: " _
    & Application.Profiles.Item(i).Name _
    & ", Host: " & Application.Profiles.Item(i).HostName _
    & ", Port: " & Application.Profiles.Item(i).Port
Next
Application.Quit

And here's an example of the output:

C:\Examples>cscript ShowProfiles.vbs
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

Enterprise Guide, Version: 4.3.0.0
Metadata profiles available for sascrh
----------------------------------------
Profile available: Null Provider, Host: , Port:
Profile available: t2817, Host: t2817, Port: 8562
Profile available: uitsrv02, Host: uitsrv02.na.sas.com, Port: 8561
Profile available: uitsrv04, Host: uitsrv04.na.sas.com, Port: 8561

 

Post a Comment

Reaching a dubious peak

Chris Hemedinger
Chris Hemedinger|January 27, 2012

So many of us struggle with this mountain. In fact, 68.27% of us get within sight of reaching the summit (while 95.47% of us are at least on a perceivable slope). We run, walk, crawl and sometimes slide our way uphill (from one direction or the other) until we finally reach the top.

That is, the top of the "bell" curve.

I came across this t-shirt design over at shirt.woot.com, one of a number of entries that celebrate dubious honors. (Also worth a look: Least Noticed Person Ever and Duck, Duck, Goose Champion.)

The design inspired me. I thought, "I'm an average sort of SAS programmer; this is a summit that I can actually reach." And with a middling amount of effort, I met my objective.

And you can do it too. But please, don't knock yourself out. I sure-as-heck didn't. In fact, I reached this dubious peak by climbing over the backs of others, lifting the code for generating a normal distribution from Rick, and the code for vector plots from the book by Sanjay and Dan.

Here's my less-than-original SAS program that yields a less-than-original design:

ods graphics /width=500 height=500;
 
data normal;
  do x = -3 to 3 by 0.1;
    y = pdf("Normal", x);
    output;
  end;
  x0 = 0;
  y0 = .43;
  you="YOU'VE ARRIVED!";
  output;
run;
 
proc sgplot data=normal noautolegend;
  title "Congratulations!";
  title2 "You've reached...";
  footnote "MEDIOCRITY";
  series x=x y=y;
  vector x=x0 y=y0 /
    xorigin=x0 yorigin=.5  
    arrowdirection=out 
    lineattrs=(color=red thickness=1) 
    datalabel=you;
  xaxis grid display=(novalues);
  yaxis grid display=(novalues);
  refline 0 / axis=y;
run;
Post a Comment

Export to Excel 2010 just got a little bit easier

Chris Hemedinger
Chris Hemedinger|January 25, 2012

In case you missed it, the first maintenance release for SAS 9.3 was recently released. Because we're all friends here, you may call it "SAS 9.3M1" for short.

Maintenance releases are usually about fixing problems that SAS has found or that customers have reported. However, sometimes a new capability might sneak out the door along with it. That's what happened this time with improved XLSX support in PROC EXPORT.

Now for the first time, this round-trip import/export cycle works just as you would expect with no additional setup required:

proc export 
  data=sashelp.prdsale 
  dbms=xlsx 
  outfile="c:\temp\prdsale.xlsx" 
  replace;
run;
 
proc import 
  datafile="c:\temp\prdsale.xlsx" 
  dbms=xlsx 
  out=work.prdsale 
  replace;
run;

Remember: using the IMPORT and EXPORT procedures to read and write Microsoft Excel files requires the SAS/ACCESS to PC Files module. With these latest changes, you can get the job done without setting up a PC Files Server, even on systems that don't have a 32-bit Windows architecture.

Prior to this support, most customers who run SAS for 64-bit Windows or on a UNIX platform would need to use DBMS=EXCELCS for this operation. The EXCELCS method works by delegating the Excel read/write operation to another Windows node that has a PC Files Server instance. (On 64-bit Windows, you can make it work with very little configuration using the "autostart" capability.)

The PC Files Server is still very useful for other scenarios, such as supporting the PCFILES LIBNAME engine, which can read and write Microsoft Excel and Microsoft Access files, among others.

And if you're looking for a point-and-click method for creating XLSX files in SAS Enterprise Guide, we cracked that nut a while ago.

Post a Comment

Splitting hairs among the ranks

Chris Hemedinger
Chris Hemedinger|January 19, 2012

This morning I logged onto my e-mail at 6:45 AM to learn that SAS was ranked as the No. 3 Best Company to Work For.

No. 3 is not as high as No. 1.  But it's very, very close.  Perhaps even barely distinguishable, in the larger scheme of things.

I couldn't head right into the office today to celebrate the achievement because I had a prior commitment.  I was once again volunteering as a science fair judge, where I had an opportunity to do a bit of ranking of my own.

I'm always impressed by the high quality of student projects at the science fair.  The kids obviously work hard, and they learn a lot.  As a judge, I wish I could award each one a special prize.  But I can't.  My main deliverable is a ranking: 1st, 2nd, 3rd and Honorable Mention -- drawn from a pool of dozens of projects.

The task of picking the best is easier than you might think.  The "cream" rises to the top.  Yes, all of the kids work hard and have good projects.  But just a few of them really stand out.  They pick the most thoughtful experiments.  They have the amazing display boards, with clear data and graphs. When interviewed, the stand-out kids can answer every question you pose and demonstrate their above-average insight.

Yes, it's easy to pick the best set.  The hard part is ranking those best four into 1-2-3-4.  Today we had a situation where the judges could not decide between two projects for a 2nd place award.  But we could not have two 2nd places (not enough ribbons!); someone had to be 3rd.  I served as the tie-breaking vote, evaluating each project closely.  I couldn't really find a flaw with either one, so we had to ask the question "which project did a better job of reaching its potential?"  With some deliberation, we sorted it out to everyone's satisfaction.

I imagine that the Great Places To Work Institute goes through a similar process each year as they rank USA workplaces.  It may be easy enough to come up with a Top 10, but I'll bet it's tricky to rank the companies within that elite set.

But that's not a problem for me.  This year the official ranking for SAS is No. 3, but I still regard this place as No. 1.  Anyone who says otherwise is just splitting hairs.

Post a Comment

Choose your own adventure with SAS OnDemand for Professionals

Chris Hemedinger
Chris Hemedinger|January 18, 2012

In the past, getting your hands on SAS for learning purposes required one of two fortunate situations:

  • being a student enrolled in a college course (or high school!) where SAS is taught
  • working for an employer who is willing to sponsor your training, either in an official course or on-the-job.

Now, there is an affordable way for professionals to get hands-on access to SAS for a reasonable price: SAS OnDemand for Professionals: Enterprise Guide (in the USA and Canada only, for now).

This new SAS OnDemand offering complements the SAS OnDemand for Academics offering that has evolved over the past few years.  It's SAS, running on "the cloud", and you use a supplied version of SAS Enterprise Guide to access it (along with a good collection of sample data).  With SAS Enterprise Guide you can exercise most of the SAS features that you would need to practice for any career objective: learn SAS programming, hone your skills in business analytics, or use high-end statistical methods to analyze data.

CHOOSE YOUR PATH THROUGH THIS BLOG POST:

  • If you want to learn how to use SAS to query and transform data, calculate summary statistics, build graphs, create reports, and dabble in higher-end analytics -- but you don't want to have to write or understand programming code...continue on to the immediately following section, "Learning SAS without programming".
  • If you want to learn how to program in SAS, using DATA step, SAS macro language, SAS procedures and more, and you don't want to be held back by a point-and-click interface...skip this next section and go directly to read "Programming SAS with SAS Enterprise Guide".

Learning SAS without programming

SAS Enterprise Guide is often positioned as "the point-and-click interface to SAS".  Many of us were raised on the idea that "using SAS requires programming".  But it doesn't.  SAS Enterprise Guide has over 90 built-in tasks for accessing data, summarizing data, creating reports, and performing statistical analysis.

There are many popular books and training courses that show you how to get to the power of SAS without having to learn the syntax of SAS.  For example, we have books like SAS For Dummies, Little SAS Book for Enterprise Guide, and Basic Statistics using SAS Enterprise Guide: A Primer.  And there is a whole boatload of training courses on the topic.

If you've read some of the SAS Enterprise Guide tips that I've published on this blog and want to try them out, you can probably can do it with SAS OnDemand.

NOTE: If you don't want to know anything about SAS programming, skip the next section and read "SAS OnDemand for Professionals: Learn what you want, how you want"

Programming SAS with SAS Enterprise Guide

So, you're a SAS programmer?  Or you want to be one?  Perhaps you're pursuing a SAS programming certification?  With SAS Enterprise Guide, you can skip the point-and-click stuff and jump right into programming with File->New->Program.  If you've read some of the SAS programming tips that I've published on this blog, you can try them for yourself using the SAS OnDemand environment.

For some experienced SAS programmers, SAS Enterprise Guide presents a different SAS environment than what you're accustomed to.  But you can accomplish most programming tasks here, and might even find yourself more productive with the super program editor and the process flow approach for organizing your code.

You can learn more about programmer productivity in SAS Enterprise Guide by watching this SAS Talks webinar.  Or if you really want a leg up, take the course.

SAS OnDemand for Professionals: Learn what you want, how you want

Regardless of your path -- point-and-click, SAS programming, or a mix of each -- SAS OnDemand for Professionals: Enterprise Guide provides a good learning environment to gain and practice your SAS skills.

But the learning resources don't stop there.  You can use these blogs, discussion forums, and the entire SAS community to supplement your knowledge as you learn.  SAS professionals love to share their knowledge.  And you'll be proud to share what you know too, when you join their ranks.

Post a Comment

Uncovering the hidden parts of the SAS log

Chris Hemedinger
Chris Hemedinger|January 11, 2012

Before there was CNN or FOX News, people used to get their news from SAS.

At least, that's how I imagine that people kept themselves informed. What else can explain the existence of the NEWS= system option, which helps SAS admins to surface the must-know information to the SAS community?


I didn't pay much attention to the NEWS option until a customer asked that we support it within SAS Enterprise Guide. He wanted his users to see a particular message when they started a new SAS Enterprise Guide session, and it seemed to him that the NEWS option was the best way to share those critical news bulletins with his constituents.

After a bit of research, we learned that most SAS admins prefer to use more modern methods to keep their users in the loop: e-mail, company intranets, tweets, Facebook postings -- anything but the SAS log. ("You missed the department luncheon? What's the matter, don't you read your SAS logs?")

Still, the request did surface a gap in the product. The NEWS option emits its messages in the SAS log when SAS initializes, but SAS Enterprise Guide users didn't have any easy method to view this startup portion of the log. In addition to spreading the NEWS, this part of the SAS log shows the activity that happens in the AUTOEXEC initialization, including library assignments, automatic macro processing, and more.

We promptly added this capability to SAS Enterprise Guide 4.3. Here's how you can see it:

In the Server List, right-click on the SAS server of interest and select Properties. (Note: the server must be connected -- that is, started -- before there is any log to view. So expand the server's node in the list if you haven't yet connected.)

The Server Properties window appears.

Select the Software tab, then the View Initialization Log button. A text window appears with the content of the startup portion of the SAS log.


While you're in there, the View SAS Server Products button might also be of interest. That shows you the list of licensed and installed SAS products in your environment. It's not comprehensive (doesn't check for every product that SAS offers) but it covers most of the common products.

Post a Comment

Pitfalls of the LAG function

Chris Hemedinger
Chris Hemedinger|January 3, 2012

In the immortal words of Britney Spears: Oops! I did it again.

At least, I'm afraid that I did. I think I might have helped a SAS student with a homework assignment, or perhaps provided an answer in preparation for a SAS certification exam. Or maybe it was a legitimate work-related question; I'd like to think so, anyway.

This time, the question came to me via LinkedIn. (By the way, LinkedIn contains a rich network of SAS professionals; in her blog post, Tricia provides some helpful guidance for making use of that network.)

The question pertains to some confusing behavior of the LAG function. Within a DATA step, the LAG function is thought to provide a peek into the value of a variable within a previous observation. But in this program, the LAG function didn't seem to be doing its job:

data test;
  infile datalines dlm=',' dsd;
  input a b c;
  datalines;
4272451,17878,17878 
4272451,17878,17878 
4272451,17887,17887 
4272454,17878,17878 
4272454,17881,17881 
4272454,17893,17893 
4272455,17878,17878 
4272455,17878,18200 
run;
 
data testLags;
  retain e f ( 1 1);
  set test;
  if a=lag(a) and b>lag(b) then
    e=e+1;
  else if a^=lag(a) or lag(a)=. then
      e=1;
  if a^=lag(a) or lag(a)=. then
      f=1;
  else if a=lag(a) and b>lag(b) then
      f=f+1;
run;
 
proc print data=testLags;
run;

The questioner thought that the e and f variables should have the same values in each record of output, but they don't. The two variables are calculated using the exact same statements, but with the seemingly-exclusive IF/THEN conditions reversed. Here's the output:

Obs    e    f       a         b        c

 1     1    1    4272451    17878    17878
 2     1    1    4272451    17878    17878
 3     2    2    4272451    17887    17887
 4     1    1    4272454    17878    17878
 5     2    1    4272454    17881    17881
 6     3    2    4272454    17893    17893
 7     1    1    4272455    17878    17878
 8     1    1    4272455    17878    18200

There is a SAS note that warns of the effect of using the LAG function conditionally. But in this example, each set of LAG functions are used unconditionally (before the THEN clause). Or are they?

Let's review how the LAG function works. It draws values from a queue of previous values, and within each DATA step iteration that you call the LAG function, it draws a previous value from the queue. The trick here is that this program does not call the LAG function for both A and B with each iteration of the DATA step! Because the IF statements combine two conditions with an AND, if the first condition resolves to false, the second condition is not evaluated. After all, in logic-speak, FALSE AND (ANY value) is always FALSE, so the DATA step can save work by not bothering to evaluate the remainder of the expression.

  if a=lag(a) /* if false*/ and b>lag(b) /* then this is not evaluated*/

And then the next time around, when the LAG(b) function is called again, it's "behind" one on the queue for the value of b.

One way to solve the issue (and remove the logical ambiguity): set two temporary variables to LAG(a) and LAG(b) at the start of the DATA step, and use those variables in the subsequent comparisons. With the LAG function now being called with each iteration no matter what, the results are consistent. Here's an example of the modified program:

data testlags2(drop=laga lagb);
  retain e f ( 1 1);
  set test;
  laga = lag(a);
  lagb = lag(b);
  if a=laga and b>lagb then
    e=e+1;
  else if a^=laga or laga=. then
      e=1;
  if a^=laga or laga=. then
      f=1;
  else if a=laga and b>lagb then
      f=f+1;
run;

Here are the new results when printed:

Obs    e    f       a         b        c

 1     1    1    4272451    17878    17878
 2     1    1    4272451    17878    17878
 3     2    2    4272451    17887    17887
 4     1    1    4272454    17878    17878
 5     2    2    4272454    17881    17881
 6     3    3    4272454    17893    17893
 7     1    1    4272455    17878    17878
 8     1    1    4272455    17878    18200

Post a Comment

Traffic report: the most visited posts of 2011

Chris Hemedinger
Chris Hemedinger|December 22, 2011

Lots of the visitors to this blog arrive here by way of Google search (welcome!). Thanks to search engines and a few well placed keywords, the same older posts (let's call them "timeless topics") seem to attract the most traffic from year to year. I hope that the searchers find what they're looking for; I suspect that new parents who are searching for the perfect baby name are disappointed.

In this entry, I'll list the 10 most-visited SAS Dummy posts that were written in 2011. To demystify the results a bit, I'll also include comments about why I think they drew so much traffic. Here they are (in no particular order):

Calling Windows PowerShell from SAS: a simple example
This is a cool topic for sys admin types, and I'd like to think that this is why it drew so many visitors. However, I know the real reason for its high traffic -- it was featured on the support.sas.com home page for several weeks. Any article that occupies a link from support.sas.com gets an automatic lift, because all SAS customers visit the support site, sooner or later. And many of them are like me: they are easily distracted by clicking on topics they didn't set out to find.

SAS Enterprise Guide options: my favorite 5
SAS Enterprise Guide offers over 150 options to affect its appearance and behavior. Many business users are afraid to change the default settings, or don't even know which settings are available. This post is my "best practice" guidance for venturing into the options settings.

10 tips for organizing your SAS Enterprise Guide projects
This is another "best practice" post that our SAS trainers have been promoting. Yes, you've got the software, and maybe you've been trained in the mechanics of using it for work. But what behaviors will help you to get the most out of your investment?

Measuring the value of my DVD-by-mail movie service
I had this post in my pocket for a long time -- over a year. When Netflix announced its new pricing structure earlier this year, it was the perfect time to show SAS users how to apply our tools and techniques to a relevant question that was on the mind of many.

Using SAS Enterprise Guide to run programs in batch
SAS Enterprise Guide supports a very rich (and underutilized!) automation model. Most users are familiar with it only through the very coarse-grained "Schedule Project" feature. The information in this post, and another that uses Windows PowerShell, will be featured in a SAS Global Forum 2012 paper that I'm preparing.

How to add HTMLBlue to your list of styles in SAS Enterprise Guide
With the release of SAS 9.3, you can use your current version of SAS Enterprise Guide 4.3 to take advantage of new features. The HTMLBlue style is one new aspect of SAS 9.3 that you might want to use right away. This post was also featured on support.sas.com as well as in the August issue of the SAS Tech Report. This additional visibility certainly contributed to the traffic.

Are you too good for code reviews?
This was by far my most-visited post of the year, but not just by the SAS community. This post was picked up on slashdot.org and generated much discussion there, and drew many new visitors to our SAS blogs.

Inspecting SAS macro variables in SAS Enterprise Guide
This post is just a month old, but I've been very happy with the engagement: lots of comments and tweets. "Custom tasks" is one of my favorite topics, and my hope is that this macro viewer task (and a more recent SAS options viewer) will help long-time SAS users to appreciate the capabilities of SAS Enterprise Guide.

Google Reader bundles for SAS-related blogs
Looks like people are hungry for SAS blogs, and hundreds of you have subscribed to my shared Google Reader bundles. They include over 60 SAS-related RSS feeds.

Using 3rd party shape files to build map charts in SAS
This is the answer to a question that I received while visiting customers in Australia. As soon as I learned the answer from our in-house mapping experts, I published the post. I then sent it on to the customers in Australia (and SAS support staff there) to pass the word. Of course, the techniques in the post can be used by anyone in any locale. I was pleased to find Australian map files that are freely available; it made for a good example.

Are you a regular visitor here at The SAS Dummy? If so, which post did you find the most useful, informative, or interesting? And what would you like to see more of? (I'm asking partly to be polite. You might have already guessed that I'm the type of blogger that covers the topics that are interesting to me. But I would also like to know what you find useful. Really!)

Post a Comment