Feb 9, 2010

Identify SAS Variable values prefixed / starting with an alphabet...Datastep and SQL methods...

In SAS, Inorder to identify SAS Variables starting with an alphabet one can use the colon modifiers (i.e. =: ‘Alphabet’, >: ‘Alphabet’ and <: lphabet="" span="">) to test for character strings prefixed with the alphabet.
For example...
 
/*Eg. 3 records starting with J for char variable a1*/
 
data example;
length a1 a2 a3 a4 $ 10;
input a1 a2 a3 a4;
cards;
Johnson Ketan Mike Kites
John Sawyer Sharon Michael
Joe Kitten Sue Lula
Sharon Michael SAS Doug
Pamela Tiger Woods Peyton;
 
run;
 
In a Data step...
/* We can use the : to identify obs for which a1 starts with J */
 
data finder1;
set example;
if a1=:'J'; /*keep obs whose a1 values start with J*/
run;
 
 
In a Proc step...
 
proc print data=example;
var a:; /*display variables that start with 'a' i.e. a1,a2,a3,a4*/
where a1=:'J';/*keep obs whose a1 values start with J*/
In a SQL step…
However in SQL, the datastep colon modifiers (i.e. =: ‘Alphabet’, >: ‘Alphabet’ and <: lphabet="" span="">‘)  do not work….But fortunately SAS provided string comparison operators as below to achieve similar tasks…
Truncated string comparison operators are used to compare two strings. They differ from conventional comparison operators in that, before executing the comparison, PROC SQL truncates the longer string to be the same length as the shorter string. The truncation is performed internally; neither operand is permanently changed. The following table lists the truncated comparison operators:

Truncated String Comparison Operators
SymbolDefinitionExample
EQTequal to truncated stringswhere Name eqt ‘Aust’;
GTTgreater than truncated stringswhere Name gtt ‘Bah’;
LTTless than truncated stringswhere Name ltt ‘An’;
GETgreater than or equal to truncated stringswhere Country get ‘United A’;
LETless than or equal to truncated stringswhere Lastname let ‘Smith’;
NETnot equal to truncated stringswhere Style net ‘TWO’;
/* The same as above but with eqt comparison operators */
 
proc sql;
create table finder2 as
select * from example
where a1 eqt 'J'; /*keep obs whose a1 values start with J*/
quit;

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.