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".
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;
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;
/*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
;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: