DATA Step, Macro, Functions and more

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

Reply
New Contributor
Posts: 2

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


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".

SAS Employee
Posts: 15

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

Posted in reply to IanatAIHW

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;
PROC Star
Posts: 1,759

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

Posted in reply to IanatAIHW

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;

    

  


Occasional Contributor
Posts: 5

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

Posted in reply to IanatAIHW

/*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
;

PROC Star
Posts: 1,759

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

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;
Ask a Question
Discussion stats
  • 4 replies
  • 266 views
  • 0 likes
  • 4 in conversation