This is one of the classic SAS errors that you can get from PROC SQL and it can be thrown by a number of things. Missing out a comma in a list of variables on a SELECT statement is one situation that will do it, as will having an extraneous one. As I discovered recently, an ill-defined SAS function nesting like LEFT(TRIM(PERIOD,BEST.)) will have the same effect; notice the missing PUT function in the example. The latter surprised me because I might have expected something more descriptive for this as would be the case in data step code. In the event, it took some looking before the problem hit me because it’s amazing how blind you can become to things that are staring you in the face. Familiarity really can make you pay less attention.
Archive for Programming
Reading data into SAS using the EXCEL and PCFILES library engines
Recently, I had the opportunity to have a look at the Excel library engine again because I need to read Excel data into SAS. You need SAS Access for PC Files licensed for it to but it does simplify the process of getting data from spreadsheets into SAS. It all revolves around setting up a library pointing at the Excel file using the Excel engine. The result is that every worksheet in the file is treated like a SAS dataset even if there names contain characters that SAS considers invalid for dataset names. The way around that is to enclose the worksheet name in single quotes with the letter n straight after the closing quote, much in the same way as you’d read in text strings as SAS date values (’04MAR2010′d, for example). In order to make all of this more, I have added some example code below.
libname testxl excel ‘c:\test.xls’;
data test;
set testxl.’sheet1$’n;
run;
All of the above does apply to SAS on Windows (I have used it successfully in 9.1.3 and 9.2) but there seems to be a way of using the same type of thing on UNIX too. Again, SAS Access for PC Files is needed as well as a SAS PC Files server on an available Windows machine and it is the PCFILES engine that is specified. While I cannot say that I have had the chance to see it working in practice but seeing it described in SAS Online Documentation corrected my previous misimpressions about the UNIX variant of SAS and its ability to read in Excel or Access data. Well, you learn something new every day.
A few more SAS functions to know
There are whole pile of SAS functions for testing text strings that hadn’t come to my attention until this week. Until then, I’d have gone about using functions like INDEX and PRXMATCH functions for the same sort of ends but it’s never any load to have a few different ways of doing things and to use the right one for the job. Here’s a quick list of my recent discoveries:
ANYALNUM: First position of any alphanumeric character, returns 0 if absent
ANYALPHA: First position of any alphabetic character (letter of the alphabet), returns 0 if absent
ANYCNTRL: First position of any control character, returns 0 if absent
ANYDIGIT: First position of any numeric character, returns 0 if absent
ANYFIRST: First position of any character that can be used as the start of a SAS variable name when VALIDVARNAME is set to V7, returns 0 if absent
ANYGRAPH: First position of any printable character that isn’t white space, returns 0 if absent
ANYLOWER: First position of any lowercase letter, returns 0 if absent
ANYNAME: First position of any character that can be used in a SAS variable name when VALIDVARNAME is set to V7, returns 0 if absent
ANYPRINT: First position of any printable character, returns 0 if absent
ANYPUNCT: First position of any punctuation character, returns 0 if absent
ANYSPACE: First position of any whitespace character (tabs, carriage returns and the like), returns 0 if absent
ANYUPPER: First position of any uppercase letter, returns 0 if absent
ANYXDIGIT: First position of any hexadecimal character, returns 0 if absent
NOTALNUM: First position of any non-alphanumeric character, returns 0 if absent
NOTALPHA: First position of any non-alphabetic character, returns 0 if absent
NOTCNTRL: First position of anything that isn’t a control character, returns 0 if absent
NOTDIGIT: First position of any non-numeric character, returns 0 if absent
NOTFIRST: First position of any character that cannot be used as the start of a SAS variable name when VALIDVARNAME is set to V7, returns 0 if absent
NOTGRAPH: First position of anything that isn’t a printable character that isn’t white space, returns 0 if absent
NOTLOWER: First position of anything that isn’t a lowercase letter, returns 0 if absent
NOTNAME: First position of any character that cannot be used in a SAS variable name when VALIDVARNAME is set to V7, returns 0 if absent
NOTPRINT: First position of any non-printable character, returns 0 if absent
NOTPUNCT: First position of anything that isn’t a punctuation character, returns 0 if absent
NOTSPACE: First position of anything that isn’t a whitespace character, returns 0 if absent
NOTUPPER: First position of anything that isn’t an uppercase letter, returns 0 if absent
NOTXDIGIT: First position of anything that isn’t a hexadecimal character, returns 0 if absent
Apart from simpler cases where other techniques would work well with the a similar amount of effort, there are others that would need some investigation if you were program them without using one of the above functions. For that reason, I’ll be keeping them in mind for when I might meet one of those more complex scenarios.
alphabet, alphabetic character, alphanumeric character, ANYALNUM, ANYALPHA, ANYCNTRL, ANYDIGIT, ANYFIRST, ANYGRAPH, ANYLOWER, ANYNAME, ANYPRINT, ANYPUNCT, ANYSPACE, ANYUPPER, ANYXDIGIT, carriage returns, control character, first position, INDEX, letter, letter of the alphabet, lowercase, lowercase letter, NOTALNUM, NOTALPHA, NOTCNTRL, NOTDIGIT, NOTFIRST, NOTGRAPH, NOTLOWER, NOTNAME, NOTPRINT, NOTPUNCT, NOTSPACE, NOTUPPER, NOTXDIGIT, position, program, PRXMATCH, punctuation, SAS, SAS functions, text, text strings, uppercase, uppercase letter, VALIDVARNAME, variable name, White Space, whitespace character
%sysfunc and missing spaces
Recently, I was trying something like this and noted some odd behaviour:
data _null_;
file fileref;
put “text %sysfunc(pathname(work)) more text”;
run;
This is the kind of thing that I was getting:
text c:\sasworkmore text
In other words, the space after %sysfunc was being ignored and, since I was creating and executing a Windows batch file using SAS 8.2, the command line action wasn’t doing what was expected. The fix was simple but I reckoned that I’d share what I saw anyway, in case it helped anyone else:
data _null_;
file fileref;
x=”text %sysfunc(pathname(work))”||” more text”;
put x;
run;
64-bit (Google) Gears
Gears offers the potential to speed up WordPress’ administration screens so I tend to add it to Firefox as a matter of course. However, Google only offers a 32 bit version so you are either left to your own devices with the source or you snag someone else’s efforts. I have gone for the latter and you can find the package that I used here. Thus far, my experience with the thing has been positive on Ubuntu and I plan to continue using it.