Showing posts with label dirty data management scan substr name functions sas. Show all posts
Showing posts with label dirty data management scan substr name functions sas. Show all posts

Saturday, December 23, 2006

Parsing names using scan and substr functions

Most of the times the raw data is received which requires processing before analysis. Recently, I assisted a researcher in analyzing this school data which was in the following format. The names in (brackets) is the maiden name. We have to find the everyone's name before marriage among other things.

DATA dataset ;

INPUT obs 1 name $ 2-45 idno $ 46-58 state $ 59 ;

LENGTH obs 3 ;

cards;

6RED MURAR: DID NOT COMPLETE 09/2251/0018Â 2

7MARLA (WOHL) RANDH 08/3559/0074Â 3

8RED (KRAKAS) MOHLK 07/1452/0025Â 6

9MNIT K. RAJHU 02/1758/0069Â 1

; RUN ;

/*notice the patterns which will be used to identify different variables*/
data grit;
set dataset;
roll1= SCAN(idno,-1,'/');

roll= substr(roll1,1,4);

name1= SCAN(name,1,':');

first1= SCAN(name1,1,' ');
len_temp=indexc(name,'(');
if len_temp gt 0 then do;

last1=scan(name,-1,'(');

last2=scan(last1,1,')');
last3=compress(last2," ");
maiden=trim(first1)||" "||last3;
end;
if maiden=" " then maiden=trim(name1);
proc print;
var roll name1 first1 last3 maiden;

run;

/*Pitfalls detailed here */

/* A very useful program is available here */