From | Technology Tales

TOPIC: FROM

Using the LIKE operator in PROC SQL WHERE clauses in SAS

26^th November 2025

Recently, I was working in SAS and decided to trying picking out datasets and variables from its dictionary tables, eventually picking out the maximum length of a variable type for assigning the length of a new variable. This could have been done using a long-established technique:

proc sql;
    select distinct memname into :dsns separated by '#'
        from dictionary.tables
            where lowcase(libname) = 'work'
            and index(lowcase(memname), "r_") = 1
            and index(lowcase(memname), "visit") = 0;
quit;

The result is that it creates a macro variable containing a delimited list of work datasets with names beginning with r_ and not containing the string visit. As well as using the index function to find the placing of one string within another, I have seen the count function used for similar purposes, albeit without the placement specificity. Since the =: operator which looks for a search string at the start of a larger is not something that works in SQL (data step is more than fine), you cannot do something like this:

proc print data = sashelp.vmember noobs;
    where lowcase(libname) = 'work'
    and lowcase(memname) =: "r_";
run;

While the contains operator works similarly to the count function when it comes to search text positioning, yet another option is the like operator, and that is shown in the example below:

proc sql;
    select distinct memname into :dsns separated by '#'
        from dictionary.tables
            where lowcase(libname) = 'work'
            and lowcase(memname) like 'r\_%' escape '\'
            and lowcase(memname) not like '%visit%';
quit;

Here, % and _ are placeholder characters, with the first matching zero or more characters and the second matching one character. Thus, the underscore in r_ needs escaping to look for that pattern (otherwise, it will look for the letter r at the start of a string and a single character after it) and a backslash character (\) will cover that duty. To ensure that it does what you want, adding escape '\' after the expression tells SAS what is happening.

Another thing to watch is that the percent (%) character needs a form escaping from the SAS Macro language processor, and placing the search term in single quotes attends to that. That means that %visit% does not cause any errors when you are looking for visit within a dataset name and using negation (the not operator) to exclude that possibility from the search results. However, using _%visit% might be a better pattern for finding visit at the end of a name, though.

Should you wish to play around with the above to see what happens for your own learning, try using something like this to give you a few test datasets:

data r_test r_visit visit;
    set sashelp.class;
run;

Otherwise, feel free to add your own test cases to cement the ideas even further. All too often, we look up something, deploy it and then forget about, especially when AI is involved. Nevertheless, the fastest way to write code can be to use what is embedded in your memory.

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.

Login Logger plugin

20^th July 2007

The Login Logger WordPress plugin sounds like a great idea and works fine with standard situations. However, go beyond these and things start to go awry. An example is where you have to use unique database table prefixes because you use shared hosting. This is certainly something that I do and it breaks Login Logger. Thankfully, the fix for this is easy enough: just amend the database query on line 22 in the manage.php file as follows:

Before:

$query = "SELECT distinct wp_users.user_login,".$table_name.".username FROM wp_users LEFT OUTER JOIN ".$table_name." ON wp_users.user_login = ".$table_name.".username WHERE ".$table_name.".username IS NULL";

After:

$query = "SELECT distinct " . $table_prefix . "users.user_login,".$table_name.".username FROM " . $table_prefix . "users LEFT OUTER JOIN ".$table_name." ON " . $table_prefix . "users.user_login = ".$table_name.".username WHERE ".$table_name.".username IS NULL";

The issue was caused by hard-coding of the table prefix for the user table, and using the prefix that you yourself have set is the way out of this. What is less easy to resolve is a conflict between the Login Logger and Themed Login plugins. That will take further investigation before I come up with a fix.