BookmarkSubscribeRSS Feed
IanatAIHW
Calcite | Level 5


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

4 REPLIES 4
Vladislaff
SAS Employee

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;
ChrisNZ
Tourmaline | Level 20

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;

    

  


pali
Fluorite | Level 6

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

ChrisNZ
Tourmaline | Level 20

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1027 views
  • 0 likes
  • 4 in conversation