topic Re: extract numbers in a text field in SAS Programming

extract numbers in a text field

rykwong — Sat, 27 Jan 2018 21:03:06 GMT

Hi,

I have a variable "datetext" that contains a set of numbers separated by "/".

for example patient A has datetext="2130/3457/4732"

each of the 3 numbers is the number of days of patient A to an event, so patient A has this event 3 times.

I want to extract each of the 3 numbers as a number variable so that for patient A:

date1=2130, date2=3457 and date3=4732

many thanks for your help

Raymond

Re: extract numbers in a text field

novinosrin — Sat, 27 Jan 2018 21:07:08 GMT

datetext="2130/3457/4732";
date1=input(scan(datetext,1,'/'),5.);
date2=input(scan(datetext,2,'/'),5.);
date3=input(scan(datetext,3,'/'),5.);

Re: extract numbers in a text field

rykwong — Sat, 27 Jan 2018 21:11:55 GMT

thanks so much!!

Re: extract numbers in a text field

PGStats — Sun, 28 Jan 2018 03:06:41 GMT

Note that for most analysis scenarios, you will do better in SAS with a long data structure generated by

datetext="2130/3457/4732";

do event = 1 to countw(datetext);
    daysToEvent = input(scan(datetext), event, "/"), best.);
    output;
    end;
drop datetext;

Re: extract numbers in a text field

Vipul_Sawlani — Sun, 28 Jan 2018 08:30:10 GMT

data q1;
infile datalines dlm="/";
input date1 date2 date3;
datalines;
2130/3457/4732
run;
proc print data=q1;
run;

Re: extract numbers in a text field

rykwong — Sun, 28 Jan 2018 15:42:21 GMT

many thanks!

Re: extract numbers in a text field

rykwong — Mon, 29 Jan 2018 18:41:50 GMT

if I may ask one more similar question

I have a variable "newtext" that contains a set of numbers numbers (e.g. 2,4,6,7). For example for patient A it is stored in newtext like this "[2/4/6/7]. For a separate patient, it may contain only 2 numbers such as "[4509/21]"

I want to extract each of these numbers as a number variable so that for patient A:

date1=2, date2=4 date3=6 and date4=7

many thanks for your help

Re: extract numbers in a text field

Cynthia_sas — Mon, 29 Jan 2018 18:54:19 GMT

So for the second patient, the values would be: date1=4509 and date2=21? Are those really dates? I have a hard time understanding how 4509 can be a date.

cynthia

Re: extract numbers in a text field

rykwong — Mon, 29 Jan 2018 19:01:53 GMT

these are number of days from a reference date (which was determined and set by the system). So for 4509 it is an interval of 12 years. The purpose of these is to encrypt the date. So I just need to extract these numbers into separate numeric fields. A given pt may have none of these numbers or 10 or 15 of these numbers so I am trying to figure out a code to extract accurately. many thanks

Re: extract numbers in a text field

Cynthia_sas — Tue, 30 Jan 2018 02:36:44 GMT

HI:
I agree with @PGStats -- the SCAN function will do what you want.
cynthia

Re: extract numbers in a text field

rykwong — Tue, 30 Jan 2018 10:25:16 GMT

sorry, but how do I extract the numbers out of the brackets? i.e. want to get 1, 2, 3, and 4 out of "[1/2/3/4]"

thanks

Re: extract numbers in a text field

Cynthia_sas — Tue, 30 Jan 2018 14:14:25 GMT

Hi:

Of course, you could use TRANSLATE, TRANWRD, or COMPRESS functions to disappear away the [ ] before you start, but you can do it all with the SCAN function too. Here's an example:

data showit;
  text='[1/2/3/4]';
  x1 = scan(text,1,'[/');
  x2 = scan(text,2,'/');
  x3 = scan(text,3,'/');
  x4 = scan(text,4,'/]');
  putlog _all_;
run;

Using an ARRAY and a DO loop just makes the program more elegant, however, I wanted to provide proof of concept here, not elegance.

Hope this helps,

cynthia