topic Re: find DOB within a Character field in SAS Programming

find DOB within a Character field

pandhandj — Mon, 26 Jun 2017 10:42:57 GMT

Hi Guys,

I am need of your help yet again...

i have a character field (1024) which contains a DOB which looks like this: - Date of Birth: 23/09/1947

I need to extract the DOB from the charachter field, so i can compare it to another DOB and go from there.

They date of birth is always formated as above, with the "- Date of Birth:" part there too.

I have been trying to use the "- Date of Birth:" as a delimiter, so i can then select the actual date from the field. I dont know where in the field it will appear, but it is always formatted as above.

I've been looking at Scan, Find and Index, but I'm not getting very far.

Could I ask if any one has any recomendations for me to try?

thank,

paul

Re: find DOB within a Character field

Kurt_Bremser — Mon, 26 Jun 2017 10:58:04 GMT

You might use this as a blueprint:

data have;
input field $80.;
cards;
which contains a DOB which looks like this:  - Date of Birth: 23/09/1947
;
run;

data want;
set have;
i = index(field,"- Date of Birth: ");
dob = input(substr(field,i+17,10),ddmmyy10.);
format dob ddmmyy10.;
drop i;
run;

Re: find DOB within a Character field

ChrisBrooks — Mon, 26 Jun 2017 10:58:09 GMT

Is Date of Birth always 10 characters e.g. 01/07/1947 or can it be less e.g. 1/7/1947?

Once we know that it should be a relatively simple to write something to extact the values

Re: find DOB within a Character field

RW9 — Mon, 26 Jun 2017 10:58:30 GMT

Assuming it is always that and the date is 10 characters:

data test;
  old_str="abc def llakfjfj  - Date of Birth: 23/09/1947  xyz b";
  dob_txt=substr(old_str,index(old_str,'- Date of Birth:') + 17,10);
run;

Re: find DOB within a Character field

PeterClemmensen — Mon, 26 Jun 2017 10:58:38 GMT

Something like this?

data _null_;
charDOB = "- Date of Birth: 23/09/1947";
DOB = input(strip(tranwrd(charDOB, "- Date of Birth: ", "")), ddmmyy10.);
format DOB ddmmyy10.;
put DOB;
run;

Re: find DOB within a Character field

pandhandj — Mon, 26 Jun 2017 12:37:41 GMT

All,

thank you very much for your reposnes, it really is appreciated.

i have been concentrating on Kurt's suggestion, but i still have an issue -

it looks like the index statement is stopping when it hits the first blank space (" ") in my field, thus giveing a null DOB.

i.e - quite a few of these files begin with a date stamp like this -

[Jun 8 2017 9:42AM by Smith, A]

blah blah blah

- Date of Birth: dd/mm/yyy

bla, blah, blah

when i use the index statement like this: i = index(field,"- Date of Birth: "); i get a null return in the i field

if i change it to i = index(field,"[JUN"); I get a 1 in the i field

but if i change it to = i = index(field,"[JUN 8"); i get a null result.

what am i doing wrong this time?

thanks again,

paul

Re: find DOB within a Character field

Kurt_Bremser — Mon, 26 Jun 2017 12:48:34 GMT

When you use the index() function, the excerpt (see the documentation) must appear exactly as used.

index("[Jun  8 2017","[Jun 8")

will return zero, while

[index("[Jun  8 2017","Jun  8")

will return 1.

Re: find DOB within a Character field

pandhandj — Mon, 26 Jun 2017 13:15:31 GMT

Kurt,

you are right on the money, again.

i had a leading " " in my text.

thanks too all for taking the time to help me.

best regards,

paul

Re: find DOB within a Character field

kiranv_ — Mon, 26 Jun 2017 13:38:00 GMT

using regular expressions you can do this way

data want;
set have;
dob = input(prxchange( 's/.*(\d{2}\/\d{2}\/\d{4}).*/$1/', -1, field), ddmmyy10.);
format dob ddmmyy10.;
run;