Functions can be used in fuzzy match

Notes taken from PharmSUG seminar

SOUNDEX (Sound Alike)
Ignore case, embedded blanks and punctuations; works best with English-sounding names;

Example:
WHERE varx = * "Michael";


SPEDIS (Spelling Distance)
Translating a keyword into a query containing the smallest value distance;

Example:
Spedis_Value = SPEDIS (title, "Michael"); (exact match is value 0)


COMPELVE (Levenshtein Edit Distance)
Provides an indication of how close tow strings are;

Example:
COMPLEV (Category, "Drama") as Complev_Number; (exact match is value 0)


COMPGED (Generalized Edit Distance)
Measure of dissimilarity between two strings;

Example:
COMPGED (M.Title, A.Title, 'ILN') as Compged_Score; (lowest score indicates better match)
I: ignore case, L: ignore leading blank; N:ignore quotation mark

Create random number using call ranuni

retain seed 111618;
call ranuni(seed, ranno);

VVALUE and VVALUEX functions

VVALUEX

VVALUE

How to perform a fuzzy match using SAS functions

Click Here

Perl Regular Expressions in SAS

An Introduction to Perl Regular Expressions in SAS 9

SAS 9 – Perl Regular Expressions Tip Sheet

Use BYTE function to create special ASCII character, e.g. plus/minus sign

data _null_;
      do i=0 to 255;
            x=byte(i);
            put i= x=;
      end;
      y=rank('a');
      put y=;
run;

BYTE function results:
i=65 -- 90 x=A -- Z

i=97 -- 122 x=a -- z

i=33 x=!
i=34 x="
i=35 x=#
i=36 x=$
i=37 x=%
i=38 x=&
i=39 x='
i=40 x=(
i=41 x=)
i=42 x=*
i=43 x=+
i=44 x=,
i=45 x=-
i=46 x=.
i=47 x=/

i=162 x=¢
i=163 x=£
i=164 x=¤
i=165 x=¥
i=166 x=¦
i=167 x=§
i=168 x=¨
i=169 x=©
i=170 x=ª
i=171 x=«
i=172 x=¬
i=173 x=­
i=174 x=®
i=175 x=¯
i=176 x=°
i=177 x=±
i=178 x=²
i=179 x=³
i=180 x=´
i=181 x=µ
i=182 x=¶
i=183 x=•
i=184 x=¸
i=185 x=¹
i=186 x=º
i=187 x=»
i=188 x=¼
i=189 x=½
i=190 x=¾

RANK function results:
y=97

LAG function

LAG Function (SAS 9.2 Doc)

*** Use a third variable to assign the value from the previous record;

array one a b c d;
array two e f g h;
      do over one;
            temp = lag(one);
            if first.id = 0 and last.id=1 and two = . then do;
                  two = temp;
            end;
      end;

Generate random numbers

options pageno=1 nodate ls=80 ps=64;

data u1(keep=x);
      seed = 104;
      do i = 1 to 5;
            call ranuni(seed, x);
            output;
      end;
      call symputx('seed', seed);
run;

data u2(keep=x);
      seed = &seed;
      do i = 1 to 5;
            call ranuni(seed, x);
            output;
      end;
run;

data all;
      set u1 u2;
      z = ranuni(104);
run;

proc print label;
      label x = 'Separate Streams' z = 'Single Stream';
run;

Output from the CALL RANUNI Routine
              Separate      Single
Obs      Streams      Stream

1      0.23611      0.23611
2      0.88923      0.88923
3      0.58173      0.58173
4      0.97746      0.97746
5      0.84667      0.84667
6      0.80484      0.80484
7      0.46983      0.46983
8      0.29594      0.29594
9      0.17858      0.17858
10      0.92292      0.92292

KUPDATE - replace nth position

KUPDATE(argument,position,n <, characters-to-replace > )
KUPDATE(argument,position <,n > , characters-to-replace)
Inserts, deletes, and replaces character value contents.
Product: Base SAS
Document: SAS National Language Support: Reference Guide

Link: kupdate

Function to get file path & name

%sysfunc(getoption(sysin));

Used in title of footnote or PUT statement to display the location of SAS source code.

SYSIN a system option, can be look up in table sashelp.voption under UNIX.

Link: getoption
Link: sysin

ANY* and NOT* Function

The ANY* and NOT* functions find specific characters within a character string. The set of characters that the functions find is based on the translation table used by SAS, and whether the session is run in an ASCII or EBCDIC environment. To determine which characters are found in your session of SAS, use the following program. Values of 1 mean that the character is found by the function, values of 0 mean the character is not found.

data test;
      do dec=0 to 255;
           byte=byte(dec);
           hex=put(dec,hex2.);
           anycntrl=anycntrl(byte);
           anygraph=anygraph(byte);
           anypunct=anypunct(byte);
           notalpha=notalpha(byte);
           notprint=notprint(byte);
         output;
      end;
run;

proc print data=test;
run;

COUNTW Function (SAS 9.2)

Count Words in a String

proc sort data=sashelp.zipcode(keep=statename)
out=allnames nodupkey;
by statename;
run;
data words;
set allnames;
Words=countw(statename," ");
run;
proc freq data=words;
tables Words;
title1 "Number of Words in U.S. State and Territory Names";
run;

SAS Log

3999   proc sort data=sashelp.zipcode(keep=statename)
4000             out=allnames nodupkey;
4001     by statename;
4002   run;

NOTE: There were 41763 observations read from the data set SASHELP.ZIPCODE.
NOTE: 41707 observations with duplicate key values were deleted.
NOTE: The data set WORK.ALLNAMES has 56 observations and 1 variables.

4003   data words;
4004     set allnames;
4005     Words=countw(statename," ");
4006   run;

NOTE: There were 56 observations read from the data set WORK.ALLNAMES.
NOTE: The data set WORK.WORDS has 56 observations and 2 variables.

4007   proc freq data=words;
4008     tables Words;
4009     title1 "Number of Words in U.S. State and Territory Names";
4010   run;

NOTE: There were 56 observations read from the data set WORK.WORDS.

SAS Listing Output

Number of Words in U.S. State and Territory Names

The FREQ Procedure

Cumulative    Cumulative
Words    Frequency     Percent     Frequency      Percent
----------------------------------------------------------
1          42       75.00            42        75.00
2          12       21.43            54        96.43
3           2        3.57            56       100.00

Positioning the SUBSTR Function

SAS uses the SUBSTR function to extract a substring or to modify a variable's values, depending on the position of the function in the assignment statement.

When the function is on the right side of an assignment statement, the function returns the requested string.

MiddleInitial=substr(middlename,1,1);

But if you place the SUBSTR function on the left side of an assignment statement, the function is used to modify variable values.

substr(region,1,3)='NNW';

When the SUBSTR function modifies variable values, the right side of the assignment statement must specify the value to place into the variable. For example, to replace the fourth and fifth characters of a variable named Test with the value 92, you write the following assignment statement:

substr(test,4,2)='92';

Test Test

S7381K2 S7392K2
S7381K7 S7392K7

Resolve Function

%let first=Steve;
%let last=Hanks;

data _null_;
      name1='Mr '||"&first"||' '||"&last";
      name2=resolve('Mr &first &last');
      put name1;
      put name2;
run;

105 data _null_;
106 name1=‘Mr ’||”&first”||’ ’||”&last”;
107 name2=resolve('Mr &first &last');
108 put name1;
109 put name2;
110 run;

Mr Steve Hanks
Mr Steve Hanks

CAT/CATS/CATT/CATX Functions

These Functions and Call Routines can be used to join two or more strings together.
Even though we can use the concatenation operator in combination with the STRIP, TRIM, or LEFT functions, these functions make it much easier to put strings together and if you wish, to place one or more separator characters between the strings.
One advantage of using the call routines than the functions is improved performance.
Note: *Call routine executes faster than the function… in specific…

CALL CATS:
To concatenate two or more strings, removing both leading and trailing blanks.
CATS () stands for concatenate and strip.
Just think the ‘S’ at the end of the CATS as “Strip Blanks”.
Syntax: CALL CATS (result, string-1<, string-n>);

Example:

A=”ABC”;
B=” CONSULTING”;
C=”INC “;
D=” TEAM “;

FUNCTION= RESULT
CALL CATS(RESULT, A,B)= "ABCCONSULTING"
CALL CATS(RESULT, A,B,C)= "ABCCONSULTINGINC"
CALL CATS(RESULT, "HELLO",D)= "HELLOTEAM"

CALL CATT:
To concatenate two or more strings, removing only trailing blanks.
Just think the ‘T’ at the end of the CATT as “Trailing Blanks” or “Trim Blanks”.
Syntax: CALL CATS (result, string-1<, string-n>);

Example:

A=”ABC”;
B=” CONSULTING”;
C=”INC “;
D=” TEAM “;

FUNCTION= RESULT
CALL CATT(RESULT, A,B) ="ABC CONSULTING"
CALL CATT(RESULT, A,B,C)= "ABC CONSULTINGINC"
CALL CATT(RESULT, "HELLO",D)= "HELLO TEAM"

CALL CATX:
To concatenate two or more strings and removing both leading and trailing blanks and places a single space, or one or more characters of our choice, between each strings.
Just think the ‘X’ at the end of the CATX as “add eXtra blank.”
Syntax: CALL CATX (separator, result, string-1<, string-n>);

Example:

A=”ABC”;
B=” CONSULTING”;
C=”INC “;
D=” TEAM “;

FUNCTION =RESULT
CALL CATX(" ",RESULT, A,B) ="ABC CONSULTING"
CALL CATX(" ,", RESULT, A,B,C) ="ABC,CONSULTING,INC"
CALL CATX(' / ', RESULT, "HELLO",D) ="HELLO/TEAM"
CALL CATX(' *** ', RESULT, "HELLO",D) = "HELLO***TEAM"

Functions: YRDIF, DATDIF, INTCK

Using YRDIF function:
“act/act” will gives us the actual interval between the two dates.
To know the interval between two dates in Years:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      years=yrdif(sdate,edate,'act/act');
      put years;
run;

Output: 10.2535 yrs


Using DATDIF function:
To know the interval between two dates in Days:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      days=datdif(sdate,edate,'act/act');
      put days;
run;

output: 3745 days


Using the INTCK function:
The INTCK function returns the integer count of the number of intervals in years, months or days between two dates.

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      years=intck(‘year’,sdate,edate);
      put years;
run;

output:10 years


To know the interval between 2 dates in days:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      days=intck(‘days’,sdate,edate);
      put days;
run;

result: 3745 days


To know the interval between 2 dates in months:

data _null_;
      sdate="12mar1998"d;
      edate="12jun2008"d;
      months=intck(‘months,sdate,edate);
      put months;
run;

result: 123 months

MOD function

MOD (dividend-expression, divisor-expression)

Returns the remainder from the division of dividend-expression by divisor-expression.

Statements	Results
a=mod(10,3);	1
a=mod(.35,-.1);	0.05

Sample program: (select certain observations from a dataset)

data temp;
       set temp;
       if mod(_N_,3)=0;
run;

Calculate Age

%macro age(date,birth);

floor((intck('month',&birth,&date)
    - (day(&date) < day(&birth))) / 12);

%mend age;

Compress

Strip off some typical special characters:

test2=compress(wordvb, '0D'x);
*** remove the carriage return;

test2=compress(test2, '0A'x);
*** remove the line feed;

test2=compress(test2, 'A0'x);
*** remove non-breaking space;

varname = compress(varname, , 'kw');
*** The modifier “k” stands for ‘KEEP’ and the modifier “w” stands for ‘WRITABLE’. Note that there is no second parameter in the above code. When compress function is used in combination of K & W modifiers, it keeps all the writable characters which means it deletes all the non writable characters.