Summing Missing Values

Adding two figures and get a different result from your expectation. Yes this is possible with SAS when missing or special missing values are included in your records. Let’s find out the difference between mathematics operators and functions which deals with computation taking the SUM function as an example.

Here is the rule: SAS functions ignore missing values in their computation. So with the SUM function, the sum of 2 and a missing value is 2, whether it is missing with the + operator: 2 + . = .

Now, I shall detail the three SUM function notation.

1. List values as individual SUM function parameters: parameters of a SAS functions are delimited with commas. To compute the sum of several values, one can list them one after another withint the SUM function brackets using commas as delimiter.

newvar = sum(2,3,.);

Most of the time, the programmer won’t compute raw figures but will refer to values through variables:

newvar = sum(x,y,z);

2. Use the OF keyword to list values without commas : to list the values to sum without comma, the OF keyword must be used.

newvar = sum (of 2 3 .) ;

newvar = sum(of test2 test3 test4);

3. Extension of the notation with the OF keyword : in the previous example variables have a name a common basis and an integer incremented by 1. By defining an interval, listing the first and lasst variable joined by an hyphen, you should be able to save you from a work which become more and more boring as the number of variables increases.

newvar = sum(of test2-test4);

This notation is not specific to the SUM function. It can be used in many occasions such as the keep or drop function, an array, etc.

Important: Please note how important it is not to forget the OF keyword. In the previous example, SAS would substract variables test4 to variable test2 if the OF keyword would be missing. The sum function would then have a single value.

Further Reading : the MIN and MAX functions and their related operators >< and <> deal the same way as the SUM function and its + operator with missing values.

Uppercase or Lowercase with SAS?

When a programming language does not deal the same way with words having different cases, one says it is case-sensitive. What about SAS? Is it case-sensitive?

1. Words not affected by changes in case: most of the time SAS does not deal differently whether uppercase or lowercase is used. Here are a few examples:

• SAS syntax (data, proc, do, if, filename, etc.)
• SAS library names (libname orig_data ‘C:/’;)
• SAS data sets (data demo; set DEMO;)
• Variables name (age, AGE, Age)

If you look into you metadata using SAS dictionaries or proc contents procedure, you’ll see that all library names follow a standard notation regardless the original input name. Please note the exception of variable names which still appear in dictionaries with their original notation.

Tip: Lowercases are easier to read. Therefore you’re highly recommended to use them. You can save uppercases for special occasions, for recognising bits in your code.

2. Quoted strings are case-sensitive: here are three case-sensitive examples:

• Values of character variables (cntry=’UK’;)
• Variables label (label cntry=’Country’;)
• Data sets labels (data demo (label=(’Demography Panel’);)

Those three values are strings defined within quotes. Therefore, cntry=’UK’ is different from cntry=’Uk’.

3. Avoid mistakes using two SAS functions, UPCASE and LOWCASE: to check the value of a variable regardless its case, you can make your comparison of standardised records (all uppercased or all lowercased). Otherwise you may end up missing out some records. Two functions are available UPCASE() and LOWCASE().

4. Example using the UPCASE function with a dictionary: I personally use the UPCASE and LOWCASE functions to select records from SAS dictionaries. Here is an example where all records from the TABLES dictionary are selected when they refer to the WORK library.

proc sql ;
select *
from dictionary.tables
where upcase(libname)=’WORK’;
quit;

4 Functions to Remove Blanks

1. Remove leading and trailing blanks using the STRIP function

Understand the old SAS programs and the TRIM and LEFT functions: until SAS 8.2 version, to remove leading blanks from a string, we would have first more them to the end of the string using the LEFT function and remove those trailing blanks with the TRIM function. This has several disadvantages:

• Two functions had to be used (TRIM and LEFT),
• The order the functions appears had to be respected. Otherwise the trialing blanks would remain.
• When this notation was used with am empty string, the result was a string of length 1 instead of 0. The TRIMN function which solves this issue only appeared with SAS 9.

Making the most of the new syntax, the STRIP function: since SAS 9, the STRIP function resolves the three issues. This function is equivalent to TRIMN(LEFT()).

2. Keep an unique blank between words using the COMPL function: usually a single blank between words of your strings is needed. To remove any unecessary blanks, you can remove the COMPL function. If there are many trailing blanks, only one will remain.

3. Remove all the blanks using the COMPRESS function: by default the COMPRESS function remove all the blanks from a string. To remove any other type of character, you can add a second parameter. But this is beyond today’s topic.

4. Remove les x first blanks using RXPARSE et CALL RXCHANGE statements: the following notation can remplace any tect by another one. Here I shall restrict the discussion to blanks. First you have to define the value before and after in RXPARSE function. Then you define the three or even four parameters of the CALL RXCHANGE statement i.e.:

• recall the definition of RXPARSE,
• precise teh variable to update
• specify the number of time to conduct the update.
• by default the original variable is updated. To create a new variable, a fourth parameter to CALL RXCHANGE can be added. The length of the new variable will be 200 unless it has been specifically defined beforehands.

Tip: Given that it cannot be more changes than characters in the string, the length of the string can be used to define the number of repetitions. This length, when it inlcude trailing blanks and resolve to 0 when no character is available can be found using the LENGTHC function.

Example: here is a variable y with length 30, which is set based on the x variable without its six blanks.

data one;
length y $30;
x= ‘ ZZZ ABCD AB ‘ ;
rx=rxparse(” ‘ ‘ to “);
call rxchange (rx,lengthc(x),x,y);
run;

Job Offer: SAS Consultants/Freelancers

DataFocus GmbH is recruiting SAS consultants for its clients in the pharmaceutical industry, telecommunication and finance based in Europe. Freelancers are also welcomed.

Email: Véronique Bourcier veronique.bourcier@datafocus.de