Technology Tales

SAS Macro and Dataline/Cards Statements in Data Step

Recently, I tried code like this in a SAS macro:

data sections;
    infile datalines dlm=",";
    input graph_table_number $15. text_line @1 @;
    datalines;
    "11.1           ,Section 11.1",
    "11.2           ,Section 11.2",
    "11.3           ,Section 11.3"
    ;
run;

While it works in its own right, including it as part of a macro yielded this type of result:

ERROR: The macro X generated CARDS (data lines) for the DATA step, which could cause incorrect results.  The DATA step and the macro will stop executing.

A bit of googling landed me on SAS-L where I spotted a solution like this one that didn't involve throwing everything out:

filename temp temp;

data _null_;
    file temp;
    put;
run;

data sections;
    length graph_table_number $15 text_line $100;
    infile temp dlm=",";
    input @;
    do _infile_=
    "11.1           ,Section 11.1",
    "11.2           ,Section 11.2",
    "11.3           ,Section 11.3"
    ;
        input graph_table_number $15. text_line @1 @;
        output;
    end;
run;

filename temp clear;

The filename statement and ensuing data step creates a dummy file in the SAS work area that gets cleared at the end of every session. That seems to fool the macro engine into thinking that input is from a file and not the CARDS/DATALINES method, to which it takes grave exception. The trailing @'s hold an input record for the execution of the next INPUT statement within the same iteration of the DATA step so that the automatic variable _infile_ can be fed as part of the input process in a do block with the output statement ensure that all records from the input buffer reach the data set being created.

While this method does work, I would like to know the underlying reason as to why SAS Macro won't play well with included data entry using DATALINES or CARDS statements in a data step, particularly when it allows other methods that using either SQL insert statements or standard variable assignment in data step. I find it such a curious behaviour that I remain on the lookout for the explanation why it is like this.

Error: User does not have appropriate authorization level for library xxxx

In a world where write access to a folder or directory is controlled by permission settings at the operating system level, a ready answer for when you get the above message in your log when creating a SAS data set would be to check your access. However, if you are working on Windows and your access seems fine, then SAS' generation of an access error message seems all the more perplexing.

Unlike the more black-and-white world of UNIX and Linux, Windows has other ways to change access that could throw things off from the straight and narrow. One of them, it would appear, is to right-click on the file listing pane in Windows Explorer and select "Customize this folder..." to change how it appears. The strange upshot of this is that a perpetual read-only flag is set for the folder in question, and that flag triggers SAS authorisation errors. The behaviour is very strange and unexpected when you find it, and the quickest and easiest solution sounds drastic. This involves deleting the folder and creating a new one in its place, saving anything that you want to retain in another temporary location. An alternative approach uses the attrib command and is less invasive.

It begs the question as to why Microsoft is re-appropriating a flag used for access purposes to be used to determine whether the HTML components of a folder display have been changed or not. This is very strange stuff and does not look like good software design at all. With all the other problems Microsoft creates for itself, I am not holding my breath until it's fixed, either. There seem to be other things like this waiting to catch you out when using Windows SAS, and a good place to start is SAS' own description of the problem that I have just shared.

Transferring data between SAS and R

A question regarding the ability to transfer of data between SAS and R set me off on a spot of investigation a while back, and I have always planned to share the results of my labours. Once I managed to locate the required documentation, things became clearer with further inspection. Functions from the foreign package seem to offer the most from the data import and export point of view, so they're what I'll be featuring in this posting.

Here, I am starting with importing, and using the read.ssd function makes life so much easier for getting SAS data into R. When I discovered that the foreign package may not be loaded by default, that could be determined easily using the following command:

search()

If package:foreign isn't in the list, then you need to issue the following function call:

library(foreign)

Of course, if the foreign package isn't installed, none of this will work. It should live in the library sub-folder of the main R installation directory, but if it isn't there, then downloading the relevant binary package from CRAN is in order. Assuming that all is installed, then a command like the following will perform the needful:

read.ssd("c:/data","data1",sascmd="C:/Program Files/SAS Institute/SAS/V8/sas.exe")

This creates a temporary SAS program that converts the SAS data set into a transport file for reading by another R function that is called in the background, read.xport. From my experience, it all seems to work fairly seamlessly.

To get data out of R and into SAS is a multi-stage process, even with the foreign package. While there are other ways, using the write.foreign seems more useful than most. Here is an example function call:

write.foreign(data1,"C:/test.txt","C:/test.sas",package="SAS",dataname="data1",validvarname="V7")

While no SAS data sets are created at this stage, a text file is generated along with a SAS program for converting it into a data set. Running the SAS program is a separate step that follows the creation of the two files. Even if it is less streamlined than read.ssd, write.foreign does make it easier to transfer data into SAS than having to write a program from scratch to read in write.table output.

In summary, R can neither read nor write SAS data sets by itself, so you need SAS installed to really make things happen. SAS gets called by read.ssd and I feel that it would be better if was called by write.foreign also rather than a SAS program generated for execution later on. Even so, it is good to see some custom functionality being provided that makes life easier. There's also the hmisc package, but my experiences while working with that on S-Plus have been such that it compares less favourably with foreign on the reliability front. Saying that, things may have changed since I last tried it.

New version of SAS on the way

This is something of a newsflash posting, but this morning's issue of the SAS Tech Report newsletter has said at last when SAS 9.2 is expected to be released. Though SAS has been talking a bit about 9.2, dates were elusive and, to a point, they still are. Nevertheless, hearing the Q1 of this year is the time slot for the unveiling is better than knowing nothing at all. Am I alone in wondering if it is coming later than was planned?

Controlling what the wpgm command calls in Windows SAS

I was setting up a key mapping in SAS 8.1 such that the log and output windows are cleared and a SAS program run in the most recently used program editor window. The idea was that debugging would be easier, and the command was what you see below:

log; clear; output; clear; wpgm; submit

I was having trouble getting SAS to pick up the most recently used Enhanced Editor window, and it was opening up an old style Program Editor window in its place. If I had wanted to use that, I would have used pgm and not wpgm. What was conspiring against me was a pesky system option. Pottering over to Tools > Options > Preferences and navigating to the Edit tab brought me to the cause of the problem: the Use Enhanced Editor check box was in the clear, and fixing that set me on my way. SAS 9 could also be afflicted by the same irritation and that is where I got the screenshot that you see below where everything is hunky-dory.

Append or update?

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;

A throwback to the past: an appearance of MACROGEN

Recently, I was reviewing a log of a program being run by SAS 9.1.3 on a Solaris system and spotted lines like the following:

MACROGEN(MACRO1):   OPTIONS NOMPRINT NOMPRINTNEST

NOTE: PROCEDURE DISPLAY used (Total process time):
real time           0.73 seconds
cpu time            0.50 seconds

MPRINT(MACRO1):   SOURCE SOURCE2 NOTES;

The appearance of the word MACROGEN made me wonder if there was another system option that I had missed. A quick search of the SAS website threw up a support note that shed some light on the situation. Apparently, MACROGEN is the SAS v5 forbear of today's MPRINT, MLOGIC, and SYMBOLGEN options and would seem to be obsolete in these days. Having started programming SAS in the days of version 6, I had missed out on MACROGEN and so used its replacements instead, hence my never coming across the option. Quite what it's doing showing up in a SAS 9 log is another story: and there I was thinking that SAS 9 was the result of a full rewrite... Now, I am not so sure, but at least I know what MACROGEN is if someone ever takes the time to ask me.

Porting SAS files to other platforms and versions

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.

SAS Institute enters the blogosphere

To get to the blogs hosted by SAS Institute, all you need to do is go here. I have to say that there is quite a spread of subject matter ranging from the high-level business strategy offerings through to detailed snippets for SAS programmers. There appears to be a lot here for anyone interested in SAS and business intelligence. I must take a longer look.

Update: I have since discovered a central listing of SAS Institute RSS feeds. The list is well worth your perusal.

SAS9 SQL Constraints

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.