Technology Tales

Append or update?

25^th November 2007

SAS can generate many types of output: plain text, XML, PDF, RTF, Excel, etc. With all of these and the SAS procedures like PROC REPORT, PROC TABULATE and so on, it might seem surprising for me to say that I have been generating output with data step PUT and FILE statements. There was, of course, a reason for this: creating text files for loading into a new database-driven software application. At one stage, I also did some data interleaving at the output stage and that's when I discovered that the default behaviour for SAS FILE statements is to completely overwrite a file unless the MOD option was specified. Adding that switches on APPEND behaviour. The code below adds a header in one step, while adding data below it in another. While I know that there are better ways to achieve this, like setting up your data as you want it or using _N_ to ensure that something only appears once, here's another way. As per the Perl, there's often more than one way to do something with SAS.

data _null_;
    file ds_data;
    put "fieldtype;datasetname;datasetlabel;datasetlayout;datasetclass;datasetstandardversion";
run;

data _null_;
    set ds_ispec;
    file ds_data mod;
    line="datasetstandard;"||trim(memname)||";"||trim(memlabel)||";;;"||trim(memver);
    put line;
run;

Porting SAS files to other platforms and versions

1^st October 2007

SAS uses its transport file format to port files between operating and, where the need arises, different software versions. As with many things, there is more than one method to create these transport files: PROC CPORT/CIMPORT and PROC COPY with the XPORT engine. The former method is for within version transfer of SAS files between different operating systems (UNIX to Windows, for instance) and the latter is for cross-version transfer (SAS9 to SAS 8, for example). SAS Institute has a page devoted to this subject which may share more details.

SAS9 SQL Constraints

23^rd July 2007

With SAS 9, SAS Institute has introduced the sort of integrity constraints that have been bread and butter for relational database SQL programs, but some SAS programmers may find them more restrictive than they might like. The main one that comes to my mind is the following:

proc sql noprint;
    create table a as select a.*,b.var from a left join b on a.index=b.index;
quit;

Before SAS 9, that worked merrily with nary a comment, only for you now to see a warning like this:

WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem.

In the data step, the following still runs without a complaint:

data a;
    merge a b(keep=index var);
    by index;
run;

On the surface of it, this does look inconsistent. From a database programmer's point of view having to use different source and target datasets is no hardship but seems a little surplus to requirements for a SAS programmer trained to keep down the number of temporary datasets to reduce I/O and keep things tidy, an academic concept perhaps in these days of high processing power and large disks. While adding UNDO_POLICY=NONE to the PROC SQL line does make everything consistent again, I see this as being anathema to a database programming type. Though I do admit to indulging in the override for personal quick and dirty purposes, abiding by the constraint is how I do things for formal purposes like inclusion in an application to a regulatory authority like FDA.

A peculiarity with PROC EXPORT

10^th June 2007

I have just encountered an issue with PROC EXPORT that I did not expect to see: it needs to run in a windowing environment. The way that I found this was that I was running a SAS macro as part of a batch job in a headless UNIX session and my program stopped dead with the job needing to be killed; that returned a message containing something about SAS/FSP and SAS/AF which does explain things. Still, this was not something that I would have expected with an export to a CSV file; the behaviour sounds more what you see with the likes of PROC GPLOT or PROC REPORT. As it happened, adding the -noterminal option to the batch command line sorted things out.

Using alternative editors for SAS programming

5^th June 2007

When it comes to writing SAS programs, most use the tools that SAS gives us, be it Enterprise Guide, the Enhanced Editor or the Program Editor. While Enterprise Guide can work with UNIX SAS as the processing engine, it is very much a Windows tool and the Enhanced Editor functionality is provided through Windows-only programming (ActiveX, I seem to recall). However, that means that creature comforts are left behind you if you turn to writing SAS code using UNIX SAS; you have only got the good old-fashioned Program Editor supplied by SAS itself. However, there is a trick that you can use to make life more comfortable: SAS does allow you to submit the contents of your paste buffer (or clipboard) using the command SUBMIT BUFFER=DEFAULT and this can be assigned to a function key for ease of use (I use the same key to clear the log and output screens at the same time). In the Windows, you may need to explicitly copy the code to do this but, in UNIX, merely highlighting a section of code with an editor like NEdit will do the trick and, given that NEdit is reasonably pleasant tool for code cutting (the ability to define its macros with a spot of scripting is a definite plus point), this makes life more comfortable again.