DATA Step, Macro, Functions and more

find DOB within a Character field

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

find DOB within a Character field

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


Accepted Solutions
Solution
‎06-26-2017 09:13 AM
Super User
Posts: 6,939

Re: find DOB within a Character field

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎06-26-2017 09:13 AM
Super User
Posts: 6,939

Re: find DOB within a Character field

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 185

Re: find DOB within a Character field

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

Super User
Super User
Posts: 7,401

Re: find DOB within a Character field

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;
PROC Star
Posts: 551

Re: find DOB within a Character field

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;
Contributor
Posts: 24

Re: find DOB within a Character field

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

Super User
Posts: 6,939

Re: find DOB within a Character field

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 24

Re: find DOB within a Character field

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

PROC Star
Posts: 253

Re: find DOB within a Character field

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 112 views
  • 0 likes
  • 6 in conversation