Strange SAS Error Message

I spent far too long trying to debug a strange error message in SAS. The solution ended up being aggravatingly simple, but arriving at the solution was not. So I wanted to share the problem and solution. (I discovered this issue using SAS 9.1.3.)

MPRINT(TRANSPOSE): proc datasets nolist;
SYMBOLGEN: Macro variable OUT resolves to lib.table_name
NOTE: Line generated by the invoked macro "TRANSPOSE".
161 modify &OUT; label &NEWVAR = "&NEWLBL"; quit;
               _
               200

MPRINT(TRANSPOSE): modify lib.table_name;
NOTE: Line generated by the macro variable "OUT".
161 lib.table_name
    _______________
    22
NOTE: Enter RUN; to continue or QUIT; to end the procedure.

SYMBOLGEN: Macro variable NEWVAR resolves to count
SYMBOLGEN: Macro variable NEWLBL resolves to count: prevalence
MPRINT(TRANSPOSE): label count = "count: prevalence";
MPRINT(TRANSPOSE): quit;

ERROR 200-322: The symbol is not recognized and will be ignored.
ERROR 22-322: Expecting a name.

NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This may cause NOTE: No observations in data set.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time 0.00 seconds
      cpu time 0.00 seconds

If you start looking where the error statements start and look down, you'll miss the true source of the error.

So for those not familiar with PROC DATASETS, one of the options on the PROC DATASETS statement is the LIBRARY= option. It defaults to WORK. So this PROC DATASETS is looking only in WORK for the dataset lib.table_name. Since "lib." is not a valid part of a table name (and just the table name), it's not parsing it correctly, resulting in an error.

The error seems to cause the SAS code parser to trip and read the rest of the code incorrectly, and it looks like the following step is where the error occurs. However, it's actually the earlier step ("modify &OUT" or the resolved code, "modify lib.table_name").

The lesson is twofold: 1) Don't try to use an output library with the table name (e.g., "lib.table_name") when the LIBRARY= option on PROC DATASETS is not used. 2) Don't write a macro where an output data set can be specified (e.g., OUT=) and use PROC DATASETS without checking whether the user specified an output library. Alternatively, don't use PROC DATASETS. (There are some advantages to PROC DATASETS, so don't throw the baby out with the bathwater!)

I wasted a lot of time on this since I thought the macro could handle any type of input/output specified, but apparently that is not true. The solution for me was to remove the "lib." from the OUT= argument to the macro ("&OUT"). Since PROC DATSETS was looking in work, it found it after I made this "correction".

Happy Birthday to Me, From Me, Sorta

A few years ago, I worked at a company that uses SAS, and has a SAS server set up. I figured out that scheduling a SAS job was a bit clunky in Windows, so I mitigated the problem by setting up a SAS program run any tasks on a schedule, and then scheduling that task in Windows.

Since the program was evaluating dates and times to schedule tasks, I threw in a few happy birthday emails as a joke. A few years later, I woke up today to find this in my email inbox:

Chris,

Happy birthday! I hope you have a great day!

Sincerely,

The [Company Name] SAS Server

Well, I'm surprised that's still running! At least it wasn't sent from my old account on that server: they had at least updated who was running the job. But I'm a bit surprised they still let this go. Either they don't realize that the scheduling code contains the happy birthday emails, or they don't mind.

As a side note, the program also ran CheckLog, which reviews the SAS log output for issues, and it emailed the author about the status of the program. This was better than running CheckLog within a program, since the program could crash before it even got to the end, resulting in no status update when it was the most critical.

At any rate, it's good to  know what I wrote is still useful, even if it contains a bit of questionable code!

An Amended Quote

Today on The Writer's Almanac, Garrison Keillor read a quote by Gustave Flaubert:

"I spent the morning putting in a comma and the afternoon removing it."

I thought this could use some amending for programmers:

I spent the morning putting in a parenthesis and the afternoon removing it.

Or for you SAS programmers:

I spent the morning putting in a semicolon and the afternoon removing it.

How about SQL:

I spent the morning putting in a column and the afternoon removing it.

Or if you write HTML:

I spent the morning putting in an escaped ampersand and the afternoon removing it.

Why not Python:

I spent the morning importing a library and the afternoon removing it.

Essentially, though, these all boil down to:

I spent the morning putting in (an) arbitrary character(s) and the afternoon removing it.

For all you programmers during this holiday season: May your parentheses be matched (your quotes too!), special characters escaped, and code executable on the first run.

Update in May 2012

Last year around this time I had decided to take a risk and leave my position at the University of Wisconsin Medical Foundation for a position at the UW School of Medicine, where I was hoping to be more involved with research. This endeavor hasn't turned out as I expected, so I've decided to move on. Starting later this month, I'll be working with CPM HealthGrades, a marketing firm that is offering more and more data services to their customers.

I accepted the position at CPM HealthGrades as a Data Scientist. This is a pretty cool opportunity in a relatively new field of data analysis, using some of the same technologies behind IBM's Watson computer, the computer that bested two top-rank Jeopardy winners. I'll be learning the programming languages Python and R, and I'm really excited to put these skills to commercial and research use.

As you may know, I attended the SAS Global Forum conference last month, and I plan to keep up with the SAS community as well, by writing papers, presenting, staying in touch with user groups, and maybe even writing a book. So I plan on keeping my SAS site up-to-date; however, I may not be developing much new. Perhaps I'll come out with a Python or R site to go along with it, with translations of code back and forth between the languages. Heck, maybe I'll write a SAS macro to write Python, or a Python module to write SAS. We'll see! Stay tuned for more details!

SAS and Keyboard Shortcuts

Base SAS has an easy way to add keyboard shortcuts that not only do X typical keyboard shortcut task, but also Y SAS program task (e.g., execute a macro). This can be a bit tricky to get to work right, but I've done it with CheckLog, PrintContents, OpenTable, and other interactive macros. However, I can't seem to figure out how to do some of these same things in Enterprise Guide.

Sure, it's easier in Enterprise Guide to open a table that's been run, but what about that library with 10,000 tables where I want to open only 1? It's like searching for a needle in a haystack, not to mention the load time just to see the tables in the library (very slow at 10k tables). It's so much easier to use OpenTable, and it's even easier to use OpenTable via keyboard shortcut. I have another keyboard command to open the properties of the table (a window that is quite severely limited in SAS EG, so perhaps that wouldn't be quite as useful anyway).

And the windowing commands seem to be gone, too. I can't edit a keyboard shortcut to jump from the program to the log: In Base SAS, I use CTRL + L for the log, CTRL + J for the program ("J" only because it's easy to reach and on the same keyboard line as L), and CTRL + K for executing the CheckLog macro ("K" for checK, I guess--again, it's on the same keyboard line as J and L). Just to note: I also have the CAPS key remapped to CTRL, so that's even easier to reach for my lazy efficient hands.

If anyone has experience with doing these things in Enterprise Guide, I'd love to hear feedback on how you get things like this to work - or if you just gave up and went with the new flow. My searches online have been fruitless up to this point, so I wanted to have this post available, at least for a point of contact in case someone else is wondering about the same things I am.

Another Odd SAS Error Message

I encountered the following error message last week:

"ERROR: Invalid value for width specified - width out of range"

The code that generated it was using SQL and the PUT function to convert an ID using a format, like so:

compress(put(id, idfmt.), '. ')

Where the IDFMT format originated as a user-created format, stored in a data set. The quick and dirty solution is to add the "?" modifier to the PUT function, which is oddly documented on the INPUT function but not the PUT function:

compress(put(id, ? idfmt.), '. ')

Now why wouldn't that be documented on the PUT function page?

And what about my original problem? I've tried varying the lengths of the input column, the format, removing the compress statement, and switching to TRANWRD or PRXCHANGE. I also tried to identify the records involved, but with 35.6 million records, that was far to slow. I know that the issue occurred in the second half of the data, but beyond that is too time consuming to look for the needle in the haystack.

Finally, I figured out a solution: The original format is based on an input data set, using the CNTLIN= option on PROC FORMAT to generate the format on the fly. The data set does not have the HLO (High/Low/Other) flag to indicate what non-matching (Other) starting values would be labeled as. I added it like so:

data custom_format;

    /* Output the original records */
    set custom_format end=end;
    output;

    /* Output an additional record */
    if end then do;
        start=.;
        label=.;
        fmtname='idfmt';
        hlo='O';
        output;
    end;
run;

Then the code using the format worked, and to boot it no longer needed the COMPRESS function to remove the blanks and numeric missing values (".").

Hopefully anyone else with a similar issue will find that one of the above solutions fits their need.

SAS Global Forum Papers Online

The SAS Global Forum papers are already posted online!

http://support.sas.com/resources/papers/proceedings12/index.html

Here are my papers:

Standardized Macro Programs for Macro Variable Manipulation

An Advanced, Multi-Featured Macro Program for Reviewing Logs

Feel free to read ahead if you're attending. It might help!

SAS Enterprise Guide and the Log

I'm presenting on the CheckLog macro that I developed to review a log (or any file or list of files) for errors, warning, and other messages in SAS related to issues. To make sure everything is ready to go, I've been reviewing it to see if there's anything I can improve, especially in regard to SAS Enterprise Guide, the new(er) platform for using SAS.

I encountered a few issues between the two. First, the display manager statements (DM) no longer work in Enterprise Guide. The fix was easy, although a bit tricky: For Windows users (Enterprise Guide is currently only on Windows), I switched from using the DM POSTMESSAGE statement to a Windows API called MessageBox. The API allows greater flexibility than the POSTMESSAGE statement, e.g., I can set an icon to go along with the message. Now Windows users can tell what happened not only by the message, but the icon, too.

However, the DM statement that attempts to export the current log is still intact. I was unable to find a way to replace this, or much less export the Enterprise Guide log in a simple, programmatic fashion. Essentially, it is not possible to use CheckLog to review the "current log" as defined in the Base SAS sense. However, Enterprise Guide users can still check the "current log", but they have to make it an external log in one of two ways.

The first way is to export the log using PROC PRINTTO before the program executes. The code for this isn't too bad, and it can be easily added to any existing program:

/* Set a directory and filename for the log */

%let log=%sysfunc(pathname(work))\work.log;

/* Print the log to the specified location */

proc printto log="&LOG";

run;

/* Run some SAS code... */

proc sql;

create table test as

select *

from sashelp.vtable

;

quit;

/* Turn off printing to the file */

proc printto;

run;

/* Check the external log */

/* Note: The shadow is set to "N" (No) since it is not necessary to copy the log */

%checklog(&LOG, shadow=N);

This method works fairly well. However, not all users may want to use PROC PRINTTO, or they may be using it in a fashion that would conflict with the above suggestion.

The second option involves the project flow in Enterprise Guide. This solution is quite a bit more convoluted, and I outlined it, along with screenshots, on the CheckLog Enterprise Guide page. Essentially, the user has to export the log as a step in the project flow and run CheckLog in a small program. From there, the user can either programmatically determine what to do or set other steps in Enterprise Guide to run based on conditions, e.g., using the ISSUES macro variable to determine what to do next.

Overall, this is quite a bit more convoluted, and it would be great if there were some programmatic way to export the Enterprise Guide log. If anyone knows of a simple way to do that, please let me know!

SQL Remerging

One of the issues that my CheckLog macro detects is the following statement:

"The query requires remerging summary statistics back with the original data."

What's going on here? Here's an example of code that generates this statement:

proc sql;
    create table patient_bp as
    select
          a.patient_id
        , a.year
        , b.exam_date
        , avg(b.bp_systolic) as bp_systolic_avg
        , avg(b.bp_diastolic) as bp_diastolic_avg
    from patient a
    left join vitals b
    on a.patient_id=b.patient_id
    where year(b.exam_date) >= (a.year-2)
    group by a.patient_id, a.exam_date
    ;
quit;

Two tables are joined, one that appears to be at the level of patient and year and another at patient and exam date. The table seeks to find any exam date greater than two years prior to the year in the patient table. The GROUP BY statement includes patient ID and exam date. However, the variable year is specified in the select statement.

Given that an extra grouper is in the data, the above query must first calculate:

    select a.patient_id, b.exam_date, avg(b.bp_systolic), avg(b.bp_diastolic)

Since the level of data, though, includes year, it must then do the following:

    select a.patient_id, a.year, b.exam_date, bp_systolic_avg, bp_diastolic_avg

Essentially, a mini-query needs to execute to create the summary variables (averages), then another mini-query to add that summary data to the main data.

So what's the issue? This seems useful. Well, the "remerging" note can obscure data issues within the data that the coder either ignored or did not expect, since only one of these statements can be generated for a SQL query. For example, the query above actually generates duplicates at the patient, year, and exam date levels, and the remerging note hints at this issue. Normally, this does not happen with "clean" data, but we rarely have clean data. Either the patient table had more than one record per patient and year, or the vitals table had more than one record per patient and exam date, which could easily occur in our system and is valid.

In the end, there are two ways to fix this and avoid the message in the log: Either split the query, like below, or add year to the GROUP BY statement (if that is acceptable - here it is, but that's not always the case). The split query is a little longer, but I've never noticed that such queries take much longer.

proc sql;
    create table _temp_ as
    select
          a.patient_id
        , b.exam_date
        , avg(b.bp_systolic) as bp_systolic_avg
        , avg(b.bp_diastolic) as bp_diastolic_avg
    from patient a
    left join vitals b
    on a.patient_id=b.patient_id
    where year(b.exam_date) >= (a.year-2)
    group by a.patient_id, a.exam_date
    ;

    create table patient_bp as
    select
          a.patient_id
        , a.year
        , b.exam_date
        , b.bp_systolic_avg
        , b.bp_diastolic_avg
    from patient a
    left join _temp_ b
    on a.patient_id=b.patient_id
    ;

    drop table _temp_;
quit;

Note that this is essentially replicating what SQL is doing in the back-end, but since it is split out, it is obvious that the programmer knew about the potential issue and purposefully coded it that way to avoid it.

Someone suggested adding "distinct" to the select statement, but the message regarding remerging does not go away when this solution is used. Thus, any other unexpected data issues may continue to be masked by this statement, since only one can be generated for each SQL step.

Any time you're doing data transformation, it's always best to test your assumptions. A good macro for testing the level of the data (input and output) is the DupCheck macro. Adding this one line of code would have told the programmer something was amiss:

    %DupCheck(patient_bp, patient_id exam_date);

The DupCheck macro and its documentation is available online.

Good luck, and be wary of remerging!

SCAPROC Bug in SAS

In my current position, we have a macro set up to run other programs, and it outputs a copy of the code, an MPRINT file, and the log. It also re-runs a project-specific autoexec to keep a copy in the log, analyzes the log for errors and warnings (using something other than my CheckLog macro), and it collects and outputs metadata. The part that finds and outputs metadata is called PROC SCAPROC. Here is how SAS describes it:

The SCAPROC procedure implements the SAS Code Analyzer, which captures information about input, output, and the use of macro symbols from a SAS job while it is running. The SAS Code Analyzer can write this information and the information that is in the original SAS file to a file that you specify.

Sounds nifty, right? Well, I've noticed some odd issues with it over time, and only recently was I able to put my finger on one of its problems.

The arrangement of code that results in the issue is rather odd and easily avoided. Nonetheless, in case anyone has encountered the problem, I thought I would post code to replicate the problem here. First, run this part in a new SAS session:

/* Set the base directory */

%let dir=%sysfunc(pathname(work));

/* Create a new folder */

%sysexec mkdir "&DIR.\lock";

/* Assign a library with a locked data set */

libname lock "&DIR.\lock";

/* Create a locked data set */

data lock.locked(pw='asdf1234');

    x=1;

run;

So we've created a new library with a locked data set based on our work library. Now, try out SCAPROC with the following code and see what happens:

/* Copy the CARS data set */
data cars;
    set sashelp.cars;
run;

/* Create a view */
proc sql;
    create view badview as
    select type
    from cars
    ;
quit;

/* Remove type from the data set */
/* This makes the view (i)nvalid */
data cars;
    set cars;
    drop type;
run;

/* Start PROC SCAPROC */
proc scaproc;
    record "&DIR.\lock\scaproc_out.txt" attr;
run;

/* Copy data out of SASHELP */
data test;
    set sashelp.vcolumn(where=(libname="WORK"));
run;

/* Write out SCAPROC */
proc scaproc;
    write;
run;

And now we've created a view from a SASHELP table and made it invalid by removing the column that the view uses in its definition. We started SCAPROC and attempted to use another SASHELP table that contains metadata about all of the columns available to SAS. Did you get a pop-up for a password at this point? If so, that's part of the bug. Next, SCAPROC ends. Take a look at the log: are there some error messages? There should be at least one regarding the invalid view. Now take a look at the output of SCAPROC. It's a bit inflated for such a small program, especially if you have a large number of libraries assigned. Assign a bunch of libraries and try the code again, and it will explode.

The issue here has a few parts: First, we need an invalid view and a fully password-protected data set. Second, we need to run SCAPROC; and finally, we need to run a DATA step against SASHELP. The invalid view causes SCAPROC, upon encountering the SASHELP.Vcolumn data set, to consider all data sets listed in the table as input to the program. Interestingly, if you replace the DATA step with SQL, the issue disappears, except for the error regarding the invalid view. Dropping the use of SCAPROC also removes the problem (obviously).

Why did the password pop-up appear? Since SCAPROC considered all data sets as input, it attempted to collect metadata on the locked data set. A sub-bug of the main bug is that SCAPROC should not query locked data sets, since it won't know the password. If you were to run it in batch mode, there wouldn't be anyone around to enter a password. Another sub-bug is that the DATA step somehow tells SCAPROC that all available data is an input.

As a consequence of this issue, we had a program run where the metadata output of SCAPROC that contained variable names was a SAS data set with 5.1 million rows at 972 MB. That was huge, and the SCAPROC output file was also huge, at 751 MB. And it's just a text file - no SAS header, column structure, anything!

I submitted the bug set to SAS, but in the meantime if anyone encounters this issue, the easiest way to fix it is to either fix the broken views, replace the DATA step with SQL, or both. Good luck, and please let me know if you encounter the issue yourself, or any related issues!

SAS Global Forum

I'm a SAS programmer, and I've always wanted to attend one of those big SAS conferences. So this year, when I heard about the SAS Global Forum, I applied for a grant from the University of Wisconsin and submitted two papers to present at the conference.

A few weeks ago I heard that I won the grant, and just last week I found out that both of my papers were accepted by the conference organizers. I'll be presenting both papers in the Coder's Corner section, with the following titles:

An Advanced, Multi-Featured Macro Program for Reviewing Logs
Standardized Macro Programs for Macro Variable Manipulation

If you plan on attending and you happen to follow this blog, please feel free to stop by and say hello! If not, I'll post the links to the papers as soon as their available from the conference organizers. For more information about the conference, go to http://support.sas.com/events/sasglobalforum/2012/index.html.

Hope to see you there!

A SAS Macro to Convert Text to a Persistent ID

I was working on a project in which we usually carried forward a code and a description for every category to make it easier on coders to select a desired category using an ID rather than the long and sometimes-changing description. I found a particular category in which no ID existed, and there were no similar categories that I could use as the basis for a new ID. So what could I do?

In a previous position, I had the same problem crop up, and I solved the problem by doing the following:

1. Extract one character at a time from the original categorical variable (e.g., 'A').
2. Convert the character to its binary representation (e.g., '01000001').
3. Convert the binary representation to the numeric value (e.g., 65).
4. Sum the final value of each character (e.g., 'ABC' = 65 + 66 + 67 = 198).

Combined, these steps only take up 3 lines of code (excluding the data step code):

do _i_=1 to length(Category);

  ID + input(put(substr(Category, _i_, 1), $binary8.), binary8.);

end;

And the process can be run as a macro program:

http://sas.cswenson.com/downloads/macros/TextID.sas

Note: Run the code in test mode (e.g., %TextID(data, var, test=Y) ) to see each step broken out into separate columns.

The result is a unique ID that will never change for the particular configuration of characters. Keep in mind, however, that the ID is not only character-sensitive, but case sensitive, so "Category" is not the same as "category". To avoid this problem, force the category into upper- or lower-case using the UPPER or LOWER functions.

Using this process, we were able to add a constant, unchanging ID for each category. The ID will not vary depending on the number of categories, the time at which the code is run, or any other variable (other than user-error, if the categories are typed by hand!).

If anyone uses this process, please let me know what you used it for! Oh, and it would be nice if you gave me credit, too!

Check the SAS Log from a Windows Context Menu

Have you ever wanted to check a SAS log from the Windows context menu? Well, now you can with a new script I developed using my CheckLog macro. Here's how:

1. Download the CheckLog SAS macro and save it to a permanent location.
2. Download the CheckLogSendTo.txt file.
3. Open the file and replace the value of the variable CheckLog with the location of the CheckLog SAS macro.
4. Save the file.
5. Change the extension of the file to "vbs".
6. Create a shortcut for the CheckLogSendTo.vbs file. Rename it as "CheckLog".
7. Copy the shortcut to the user's SendTo files here: C:\Documents and Settings\%USERNAME%\SendTo, replacing the %USERNAME% variable with the username in question.
8. Right click on a file or directory to check. Go to Send To > CheckLog.
9. Select Yes, No, or Cancel when asked whether you would like to add additional arguments.
1. Yes = Add arguments
2. No = Use the default arguments
3. Cancel = Cancel the check
10. If you said Yes above, enter the additional arguments you would like to use. See the Arguments page for more detail.

Here are some screenshots of the script in action:

Note: VBScript is required, which is unfortunately only available on Microsoft Windows. I hope to develop a platform-independent script sometime. I chose VBScript because it required no additional installation or administrator requirements on the platform I current work on.

This script should save me a lot of time! Normally I would copy the directory and filename of the log into a SAS session and paste it into a CheckLog call. But now it's more automatic, and I get the results faster.

Please feel free to send me feedback about the script!

SAS and Ampersands

I realized I'm a SAS geek today. On my way to work, near the UW and VA hospitals, I saw a street sign that displayed "UW&VA" and I wondered, "What does &VA resolve to?"

What does &VA resolve to?

So you know when you're a SAS geek when you see a sign with an ampersand immediately followed by characters, and wonder, "What does that resolve to?" Bad joke, go to your work library!

Seriously, though, this brings up a good topic: How do you resolve macro variables with special characters in the value? For example, if we have the macro variable parking_sign with the value "UW&VA", and this value is assigned automatically in a data step using the CALL SYMPUT routine or SQL using the INTO statement, neither of which would generate an error in the log regarding the value of the macro variable VA.

There are a couple of solutions to solve this and avoid an error in the log:

1. Define the macro variable VA as "VA" (i.e., %let va=VA;). This is a quick fix that allows the process to continue by creating a value for the macro variable that the code is mistakenly trying to look up. But it does not solve the problem completely, since new strings with ampersands or even percent signs could be introduced into your source data. This process could be automated to generate multiple macro variables for each string with an ampersand found, but it would not work for macro programs (which use percent signs).
2. Edit the source data and mask the characters after the ampersand (or percent sign) with the %STR (or related) function. This solution would work, but it would be rather awkward, and your source data would have a strange-looking value (i.e. "UW&%str(V)A"), which might not be a good solution if you have to use the data in a different way later, in which the %STR function would not execute, leaving the odd-looking raw value. It gets even more complicated when percent signs are involved.
3. Instead of resolving the macro variable with the ampersand, resolve it with the %SUPERQ function. This function is a better solution, since it avoids any special characters problems and does not edit the source. Basically it tells SAS to resolve only the first level of the macro variable, and no further. However, it is not as easy to implement, since it requires replacing all references to the macro variable (e.g., &PARKING_SIGN) with different code (i.e., %SUPERQ(PARKING_SIGN));
4. Use the %SUPERQ function with an additional step that redefines the PARKING_SIGN macro variable (see below). This is the best solution, since the rest of the code does not have to be re-written. The %LET statement is used to immediately redefine the macro variable using the %SUPERQ function, which will not resolve the values any further. Better yet, this is only one line of very simple code, and it fixes the problem forever!

/* Dummy data */

data test;

    input parking_sign $;

    format parking_sign $15.;

    infile datalines;

datalines;

UW&VA

;

run;

/* Generate macro variable */

data _null_;

    set test;

    call symputx('parking_sign', parking_sign);

run;

/* Use macro variable by writing it to the log. */

/* Note: Generates a (w)arning on the first run. */

%put &PARKING_SIGN;

/* Solution: Redefine macro variable */

%let parking_sign=%superq(PARKING_SIGN);

/* Use macro variable by writing it to the log. */
/* Note: This time there is no (w)arning. */

%put &PARKING_SIGN;

This last solution is by far the simplest and least amount of work to fix the problem.

For more on macro variable quoting (the %STR and %SUPERQ functions), see the SAS website:

http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#tw3514-overvwqt.htm

A New SAS Tool for Checking the Log

SAS just released a new tool for checking the SAS log:

SAS Log Error Checking Tool

It only works on Windows, requiring the .NET framework, and it appears to be a separate executable from the usual SAS program. Further, it seems to only check for ERROR and WARNING messages.

As I've written before, I have a macro, CheckLog, that checks the log, too, and it runs on any operating system, can be used within a current interactive SAS session, and it appears to check for more messages. (See the CheckLog About page for more documentation.)

If anyone tries out SAS's tool, I'd love to hear a comparison of the capabilities!

The Name of This Blog

Today I received the latest sascom magazine and found an article about retaining analytical talent. One of the items in the topic of finding talent, is "The skill set involves both sides of the brain". That's what I'm trying to write all about here!

SAS Passwords Suck

There, I said it. Apparently anyone else who uses SAS passwords hasn't had the guts to say that. Here's why I say so:

• SAS data set passwords must adhere to the rules for SAS names. These rules include:
• The first character must be a letter or the underscore (1)
• All other characters can be letters, numbers, or the underscore (1)
• The length cannot exceed 8 characters (2)
• Using PROC SQL, programmers can enter longer passwords than are acceptable, without any notification (unlike in the DATA step), and these passwords become truncated. This lets the programmer think they are applying good passwords. Worse, if someone else has to use the password other than the programmer who created it, they will find it is broken and will not be able to continue with their work.
• There is an option to encode passwords, but as the SAS documentation notes (3): "With encoding, one character set is translated to another character set through some form of table lookup. An encoded password is intended to prevent casual, non-malicious viewing of passwords. You should not depend on encoded passwords for all your data security needs; a determined and knowledgeable attacker can decode the encoded passwords." So I'm not quite sure what the point is of such an option, since it's easily hacked.
• SAS data set passwords are typically stored as plain text within SAS code. For anyone who wants to get the password, find the source code. Usually the directory is not secured in the OS.
• If the password is incorrect, a pop-up appears, which can be turned off (4). So a hacker could turn off the option and keep cycling through passwords until the correct one is reached.
• Requiring passwords on data sets implies that other, standard systems of securing and backing up data are not trusted; furthermore, it conveys a level of distrust for the SAS programmers who are using the data sets. Other than human error, what is a well-meaning SAS programmer going to do with the data?

Someone out there might say, why not use data set generations (5)? (Generations are basically automatic copies of SAS data sets that have a limit on the number of copies.) There are several reasons against this:

1. It's a waste of disk space, especially with large data sets, and especially if you have a good backup system.
2. The generations are eventually deleted. Once an item reaches the maximum number, it's deleted. If that data set was important, why would you want to delete it, ever?
3. It's confusing. There are no explicit explanation of what a generation may mean. It could be that one value was off in one generation so the data had to be re-run. It could be the whole data set was the wrong one. Why would you want to keep a history of that? It's simply garbage.

So what's a SAS administrator to do? First, use the operating system to manage rights by folder access. If only certain people should access the SAS data sets and code, lock the files for only those users. Second, implement good OS password policies. Those passwords can be by far better than SAS's! Third, back up hard drives automatically. Fourth, set a coding standard for making backups/copies in SAS before overwriting important data sets, which simply makes it easier to restore from a bad run (instead of finding the actual backup). (The DATASETS procedure is great for bulk copying and modifying SAS data sets.) Fifth, trust your programmers. If you can't trust your programmers, why are they working for you? Why do you give them important work? Do you really want to convey that to them? What does it do for their morale and self worth?

I hope by posting this SAS realizes that their password mechanism is essentially broken and useless. Even a novice hacker could figure out how to break into such a system. Other options in the system don't really help, which leads back to the OS itself. And the OS probably has much better tools for managing access and passwords that SAS does.

References

1. http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a001028606.htm
2. http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000998953.htm#a000998960
3. http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a003166704.htm
4. http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000995314.htm
5. http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000243174.htm

Writing a Portable SAS Macro

One aspect of writing macros for SAS is to try to make them portable. There are two aspects of this: First, writing the process in a manner that is generalizable so that there's a need for the macro to be portable; second, writing the program so it works on multiple platforms of SAS and operating systems (OS).

The first task I've always seemed to excel at. With almost 90 general-use macros hosted on my site, I have a macro for many useful, reusable processes. I've written code with these macros where most of the code is simply calls to these macros. They save a lot of time and repetitious code.

The second task, however, has always been a bit elusive, especially since I am unable to run my macros on different OSs. A SAS license would be required on each OS to do so, even if I had UNIX, z/OS, OpenVMS, and Windows installed where I could access them. This creates some significant problems with this last task. As a result, I have come up with a few strategies to get around the issue.

The first and most powerful is to re-write any OS-specific function or process in the native SAS language. This can be tricky since a simple process like obtaining a list of files in a directory can be a simple, one-line piece of code for the OS, but several steps (and sometimes more limited functionality) for SAS.

Another step is to simply modify the macro to run several different versions of the same thing depending on the OS. SAS has two automatic macro variables that define the operating system, SYSSCP and SYSSCPL, that can be used to run conditional code that will work in the OS. This is still tricky, since I have no way to test the code to see if it works. It helps to review help documentation, examples online, and forums for those OSs, but I'm still never 100% certain that it will work as expected.

For this last part, I rely on the users out there. I received several rounds of feedback recently on my CheckLog macro, which reviews the SAS log, an external log (even non-SAS), and an entire directory of logs, with several additional features to tweak how it runs and how it notifies the user about issues. (See the documentation for more detail.) These users were able to provide specific feedback about what didn't work, and they were able to test new versions on their system quickly. Through this process, I was able to modify the macro significantly, and now it runs on all SAS-supported OSs (UNIX, z/OS, OpenVMS, and Windows). And the users have a better utility that fits their needs with insignificant cost.

Using this model for developing macros, it could be possible to add to the standard SAS library with new and very useful processes. So if you're a SAS user, join in and contribute to the community by publishing general-use macros or by testing and providing feedback to others who have done so!

News and Advice

Last week, I started a new position with the Health Innovation Program in the School of Medicine and Public Health at the University of Wisconsin-Madison. In this position, I hope to be more involved in research that could potentially affect patient care. Ever since my days doing academic and educational research, I have missed being involved in the process.

Along with this transition, it appears I've started to become a help desk of sorts for some SAS. I received two sets of email from different users of my macros, and I thought I would start sharing those questions and answers here:

Question 1:

Is there a way to limit the number of observations read by PROC IMPORT? I tried using OPTIONS OBS=2 before proc import and it worked but still the reduction in time was not significant.

Answer 1:

It depends on the file, so you'd have to look at the IMPORT procedure documentation depending on what you're importing to see if that feature is available.

Where did you put the option? It must have been just above the output. If so, then yes, it wouldn't decrease the time, since SAS is still pulling in all of the records. Depending on the location of the option, SAS may only keep the number of records on its way out of the import process. Either way, SAS may need to read the entire file, which would reduce time.

What if you tried just pulling in the header record, and delimiting them yourself? I'm guessing you're looking at comma-delimited, space-delimited, or tab-delimited data, and you should be able to figure out how to read the data in as one record, then split the variable names into multiple variables or even records. Then you could see if there are any new records.

Question 2:

Kindly help me to create the folders using SAS codes in local drive or on server without using X-command.

Answer 2:

You can use either CALL SYSTEM within a data step or %SYSEXEC. You'll want to tinker with the XSYNC and XWAIT options as you see fit. Here are two examples:

data _null_;

    call system('md c:\TestDir');

run;

OR

%sysexec(md c:\TestDir\);

Unusual SAS Error Message

Today I encountered the following error messages in SAS after creating a table using SQL based on a SASHelp table:

ERROR: Floating Point Zero Divide.
ERROR: Termination due to Floating Point Exception

This strange set of errors is not well documented and, as I eventually found out, has nothing to do with my original query:

proc sql;
create table _test_ as
select * from sashelp.vtable
where upcase(libname)="SPECIFIC_LIBNAME"
;
quit;

Where "SPECIFIC_LIBNAME" was a macro variable containing the libname I wanted information about.

The solution to this problem was to clear a particular library that consisted only of views. This library's members were generated prior to the above step, and may have somehow contributed to the problem within the SASHelp metadata. In general, it may be a good practice to review the libraries and their sizes when encountering problems with the SASHelp library, which contains a great deal of metadata on SAS libraries.

For this set of errors, however, this solution will probably not always work. It would appear that the problem is related to extensions of SAS that are dependent on external sources. In this case, it may have been the SQL procedure. This does seem strange, since SQL is a widely-used standard. Clearing the previously-created library may have cleared up an exception caused by a large amount of data in the SASHelp table that was not accounted for in the manner in which SAS implemented the SQL standard or the proprietary extensions of it.

If you encounter this error, please let me know! Especially if you know how to fix it!

Articles and Discussions Regarding these Errors:

• Error Query
• PROC VARIOGRAM
• Integer divide by zero discussion
• Log Interpretation Help!
• Integer divide by zero question
• UCLA FAQ