Special Missing Values in SAS

Definition: Special missing value is a type of numeric missing value that enables you to represent different categories of missing data by using the letters A-Z or an underscore.

Ref: SAS 9.1.3 language reference: concepts page no: 102

The symbol usually used to represent a missing value for a numerical variable is the period or dot. Aside from the dot, there are 27 special missing values SAS can store in numerical variables. They are the dot-underscore (._), and dot-letter (.A thru .Z). Note that these special values are case insensitive. That is, .A=.a .B=.b .C=.c etc.

If you do not begin a special numeric missing value with a period, SAS identifies it as a variable name. Therefore, to use a special numeric missing value in a SAS expression or assignment statement, you must begin the value with a period, followed by the letter or underscore, as in the following example:

x=.d;

When SAS prints a special missing value, it prints only the letter. When data values contain characters in numeric fields that you want SAS to interpret as special missing values, use the MISSING statement to specify those characters.

Example:  Consider the following data step which contains a questionnaire data (three students, three questions, and three possible responses to each question 1, 2 and 3):

data test;

/* M = multiple, U = unreadable, .=Didn’t answer */

missing answer M U;

input student question answer;
datalines;

1 1 1
1 2 2

1 3 M

2 1 U

2 2 3

2 3 2

3 1 M

3 2 .

3 3 1

;

Proc print data=test; run;

The MISSING statement is needed here to keep special missing values for the numeric variable answer. In the above example, M is used to indicate multiple responses (not allowed) and U is used to indicate an unreadable response.

Order of Missing Values for Numeric Variables:

The numeric missing value (.) is sorted before the special numeric missing value .A, and both are sorted before the special missing value .Z. SAS does not distinguish between lowercase and uppercase letters when sorting special numeric missing values.

Checking for Missing Numeric Values:

Often the SAS programmer uses the following SAS code to check for a missing numeric value:

IF VALUE=. THEN PUT "*** Value is missing";

While in most instances the above code works as intended, there are occasions where it may not catch some missing values. The above statement assumes that only a dot is present, and none of the other 27 missing numeric values, are present in your data. In exhibit 1, it was shown that the dot-Z is the highest missing value. So, a better, more inclusive way to check for a missing numeric values is:

IF VALUE <=.Z THEN PUT "*** Value is missing";

Reference: http://analytics.ncsu.edu/sesug/2005/TU06_05.PDF

The latter IF statement checks for all 28 possible missing values.

For more details on Special Missing Values Please refer, Malachy J. Foley paper … MISSING VALUES: Everything You Ever Wanted to Know

The other thing you should know is... If the MISSING option is used in PROC FREQ, you'll get a breakdown for each type of missing value. For example, given (without MISSING):


*Without MISSING option:
proc freq data=test;
tables question*answer/ nopercent nocol norow;
run;

Output:

*With MISSING option:


proc freq data=test;
tables question*answer/ nopercent nocol norow missing;
run;

output;

How to create a macro variable containing a list of variables in a DATA set

Sometimes it is very handy to have a macro variable contanining the variables names of the dataset. Here are the 2 different ways you can create a macro variable with list of variables names ...

*Method1: Using Proc Contents and Proc SQL;



proc contents data=sashelp.class out=class;
run;

proc sql noprint;
select distinct(name) into:vars separated by " " from class;
quit;


%put &vars;


*Method2: Using SASHELP tables and Proc SQL;


data class;
set sashelp.vcolumn(where=(libname="SASHELP" and memname="CLASS"));
keep name;
run;


proc sql noprint;
select distinct(name) into:vars separated by " " from class;
quit;

%put &vars;

PRXMATCH Function

Prxmatch () function is very useful in locating the matching strings. Prxmatch() function has 2 parameters, the first parameter is the regular expression ID (i.e what you are looking in a string for a match) and the second parameter is the character string to be searched. PRXMATCH () function returns the start position of the matching string.
Syntax:

PRXMATCH (perl-regular-expression, source);

Even though PRXMATCH function can be used when....
1) When you want to identify if there is alphanumeric (has any letter from A to Z) in a variable.
2) If you need to search a character variable for multiple different substrings.

Here is how PRXMATCH works in the Ist case.

*Prxmatch () function is very useful in locating the matching strings;

DATA finda2z;
INPUT ID $ 1-3 string $ 5-10;
prxmatch=prxmatch("/[a-zA-Z]/",string);
DATALINES;
001 ACBED
002 11
003 12
004 zx
005 11 2c
006 abc123
;
run;


proc print;
run;

Output:

*Here PRXMATCH function will return the start position of matching string. In this case, a to z or A to Z.

If you want to find out which observation has matching string for a specified variable.

Use the following code.

prxmatch=prxmatch("/[a-zA-Z]/",string)>0;

*If match found, the value returned is 1 or else 0.

To keep those records that do not match this pattern you will look for those records where PRXMATCH returns a zero.

PRXMATCH () function is very helpful If you need to search a character variable for multiple different substrings in a variable.

Problem: Select observations where AETERM has substrings ‘ nausea’, ‘vomiting’ and ‘fever’.

The old method is to combine several INDEX function statements together with OR conditions like as …..

if index(aeterm,'nausea') > 0 or
if index(aeterm,'vomiting') > 0 or
if index(aeterm,'fever') > 0 ;

The PRXMATCH function can do this all in one statement. Less typing….few lines of code.

if prxmatch ("m/nausea|vomiting|fever/i",aeterm) > 0 ;

The 'm' option in perl-regular-expression means, PRXMATCH is going to start a matching operation.
The 'i' option tells SAS not to worry about the case, i.e, consider  "NAUSEA" as same as "nausea" while searching for a match.
Another advantage of using this 'i' modifier is we can make parts of a string case sensitive and insensitive using  ( i:)  or (-i:).
( i:)  turns ON the case insensitive search
(-i:) - turn OFF the case insensitive search

Pipes ‘|’ should be used to separate the search strings.

Please refer PRXMATCH in the Functions section of the SAS Language Reference: Dictionary in the Online SAS Documentation for more information.

PRXMATCH special characters and it's meaning:
^ - start with

$ - end with
\D - any non digits
\d - digits
? - may or may not have?
| - or
* - repeating
( i:) - turns ON the case insensitive search

(-i:) - turn OFF the case insensitive search

$UPCASEw. format

We all know the importance of UPCASE function in handling the character case strings. But do you know that a format can let you do the same as the UPCASE function (upcasing the variables).

$UPCASEw. format works similar to the UPCASE Function. It also does one more thing which UPCASE function doesn’t. i.e: $UPCASEw. format let you apply length to the variable.

Remember that w specifies the width of the output field.

Example:

*********************************************************;
data new;

*convert it to uppercase;

name="studysas blog";

format name $upcase.;

newname=put(name, $upcase32.);

*Put function let you apply $upcase format;

run;

**********************************************;

*The length of the new variable newname will be 32.

SAS Supplied Formats for SAS date 17750

Replace Missing Numeric Values using Missing Option/Proc STDIZE

MISSING OPTION
Replacing missing values with the desired value like a zero is always a challenge, especially when we have a dataset with a number of columns to standardize. The OLD WAY of doing it to write a DATA step code with  if……then statements like...
 
if var=. then var=0;
to make 0 appear instead of . (dot) in the tables output.

With the SAS option called missing you can save a lot of typing.  If you place the following SAS option code before the generation of the table output:

Option missing="0";

SAS will display 0 (zero) instead of the . (dot) on the table. In fact it can display whatever the character we would like to display for missing values.  You can use a - (line) or * (star).

Always change it back the option missing setting to default as  Option Missing='';  otherwise you may endup getting unexpected results;

Warning: Since we are using this option to display zeros instead of .(dots), you shouldn't use this option before any of  SAS procedures like PROC UNIVARIATE, PROC MEANS, PROC SUMMARY and PROC FREQ because they don’t treat missing as zero. So if you use Option Missing='0' we are telling SAS to consider missing as ZERO which is not what the above procedure generally do.

**********************************************************************************;
Proc STDIZE: Proc STDIZE is very useful in processing the missing values.  It offers a simple and very effective solution when we want to process missing values.

MISSING=

specifies the method or a numeric value for replacing missing values

REPLACE

replaces missing data by zero in the standardized data

REPONLY

replaces missing data by the location measure (does not standardize the data)

Example:

data miss;
input a b c;
cards;
1 . 3
. . 2
3 2 .
1 3 4
2 . .
. 2 .
run;


proc stdize data=old reponly MISSING=0 out=new;
var _numeric_;
run;

Output: 

 

COMPRESS: SAS Function strips characters from the string

In SAS 9.1.3 , an extra argument (MODIFIER) is added to the SAS character string function COMPRESS and these modifiers modifies the action of the COMPRESS function;
Syntax: COMPRESS(<, chars><, modifiers>) ;

Following characters can be used as modifiers.

a – Compress or Delete all upper and lower case characters from String.

ak- Compress or Delete alphabetic characters(1,2,3 etc) from String.

kd- Compress or Delete characters(aplabets) from String.( Keeps only digits).

d – Compress or Delete numerical values from String.

i – Compress or Delete specified characters both upper and lower case from String.

k – keeps the specified characters in the string instead of removing them.

l – Compress or Delete lowercase characters from String.

p – Compress or Delete Punctuation characters from String.

s – Compress or delete spaces from String. This is default.

u – Compress or Delete uppercase characters from String.
See the Example below:

data _null_ ;

string='StudySAS Blog! 17752. ' ;
string1=compress(string,'') ; *Compress spaces. This is default;
string2=compress(string,'','ak');*Compress alphabetic chars(1,2etc);
string3=compress(string,'','d') ; *Compress numerical values;
string4=compress(string,'','l');*Compress  lowercase characters;
string5=compress(string,'','u');*Compress uppercase characters;
string6=compress(string,'S','k');*Keeps only specifiedcharacters;
string7=compress(string,'!.','P');*Compress Punctuations only;
string8=compress(string,'s','i');*upper/lower case specified characters;
string9=compress(string,'','a');*Compress all upper\lower case  characters ;
string10=compress(string,'','s') ; * Compress or delete spaces;
string11=compress(string,'','kd') ; *Compress alphabets (Keeps only digits);
put string1= ;
put string2= ;
put string3= ;
put string4= ;
put string5= ;
put string6= ;
put string7= ;
put string8= ;
put string9= ;
put string10=;
put string11=;
run ;

OUTPUT:

string1=StudySASBlog!17752.

string2=StudySAS Blog
string3=StudySASBlog!.
string4=SSASB!17752.
string5=tudylog!17752.
string6=SSS
string7=StudySAS Blog 17752
string8=tudyA Blog! 17752.
string9=!17752.
string10=StudySASBlog!17752.
string11=17752