topic Re: Reading human entered data - eg - 'ten' as '10' in SAS Programming

Reading human entered data - eg - 'ten' as '10'

IanatAIHW — Tue, 25 Jun 2013 00:31:35 GMT

I have some scanned in paper form data and something that would save me a lot of time would be a way to quickly convert numbers entered as words to numbers as numbers. The WORDSnn. format converts numbers to words - eg 10 to ten but I want the equivalent the other way round in either a Function or a informat. I have other complications in that people can write any old text from a regular '10' to "ten" to "ten years too long" to "I can't remember".

Re: Reading human entered data - eg - 'ten' as '10'

Vladislaff — Tue, 25 Jun 2013 07:53:33 GMT

Hi.

Maybe try something like this:


data have; 
input str $20.; 
datalines;
ten
twenty
thirteen
Forty
SIX
FoUr
run;
data want(drop=rc num_str);
if _N_ = 1 then do;
  declare hash h();
  h.definekey('num_str'); 
  h.definedata('num_str','num_int'); 
  h.definedone(); 
  do num_int = 1 to 1000; 
    num_str=put(num_int,words50.); 
    h.add(); 
  end;
end;
set have;
num_str=lowcase(str); 
rc=h.find();
run;

Re: Reading human entered data - eg - 'ten' as '10'

ChrisNZ — Tue, 25 Jun 2013 11:06:38 GMT

This works:

data NUMBERS;                          * create translation table;
  do N=1 to 1000;
    C=put(N,words32.);
    output;
  end;
run;
data RANDOMTEXT;                       * create sample data, add junk text ;
  set NUMBERS(keep=C);
  C=catx(' ',C,'years ago');
  if ranuni(0) lt .1;
run;
data TEXT2DIGITS;                      * replace data in sample and substitute letters for numbers;
  retain DSID;
  if _N_=1   then DSID= open ('NUMBERS','i');
  if LASTOBS then RC  = close(DSID);
  set RANDOMTEXT end=LASTOBS;
  * start from largest number so we dont match the one in thirty-one before matching thirty-one;
  do OBS=1000 to 1 by -1;     
    RC=fetchobs(DSID,OBS);
    NUM=getvarc(DSID,2);
    if find(C,NUM,'it') then do;
      C=tranwrd(C,trim(NUM),put(getvarn(DSID,1),4.));
      output;
      return;
    end;
  end;
  output;                                    * no match  ;
run;

Re: Reading human entered data - eg - 'ten' as '10'

pali — Tue, 25 Jun 2013 12:10:13 GMT

/*Create a dataset to be used to create informat*/
data informat_data;
format start $100.;
fmtname ='w_to_n';
type='I';
do label=1 to 100;
start=tranwrd(put(label,words100.),'-',' '); /*Replacing - with space*/
output;
end;
run;

/*Create an informat*/

proc format cntlin=informat_data;
run;

/*Apply informat to read words as numbers*/

data test ;
input comments $50.;
number=input(compbl(lowcase(comments)),w_to_n.);
datalines;
10
twenTY FouR
Thirty nine
one
SIX
;

Re: Reading human entered data - eg - 'ten' as '10'

ChrisNZ — Tue, 25 Jun 2013 20:55:24 GMT

Your solution doesn't deal with junk text, but I like how you thought about issues caused by hyphens.

I've added that flexibility to my solution.

Also, the word format omits 'and' in strings Iike 'one hundred and one' which sas writes 'one hundred one'

I've now catered for this as well.

Lastly, translate() is better suited than tranwrd() for replacing characters.

data NUMBERS;                          * create translation table;
  do N=1 to 1000;
    C=translate(put(N,words32.),' ','-');
    output;
  end;
run;
data RANDOMTEXT;                       * create sample data, add junk text ;
  set NUMBERS(keep=C);
  C=catx(' ',C,' years ago');
  if ranuni(0) lt .1;
run;
data TEXT2DIGITS(keep= C C1);          * replace data in sample and substitute letters for numbers;
  retain DSID;
  if _N_=1   then DSID= open ('NUMBERS','i');
  if LASTOBS then RC  = close(DSID);
  set RANDOMTEXT end=LASTOBS;
  C1 =compbl(tranwrd(translate(C,' ','-'),'and',' '));
  * start from largest number so we dont match the one in thirty-one before matching thirty-one;
  do OBS=1000 to 1 by -1;     
    RC =fetchobs(DSID,OBS);
    NUM=getvarc(DSID,2);
    if find(C1,NUM,'it') then do;
      C1=tranwrd(C1,trim(NUM),put(getvarn(DSID,1),4.));
      output;
      return;
    end;
  end;
  output;                              * no match  ;
run;