Extracting Date and Time from a Character String when there are spaces

[ginak]

Hello all!

I am having trouble extracting data and time from these variables (they are all character variables):

MemberNameNum                 DOB                                       ServiceStart 

Prinze Jr,Freddie(124A)       Dec 21 1951 12:00AM          2014-07-31 00:00:00

Doe,John(100B)                   Apr  6 1955 12:00AM            2014-05-14 00:00:00

Abdul Jawad, Joe(325A)      Apr 21 1941 12:00AM          2015-07-29 00:00:00

What I would like to do is to get it in this format, where the dates and times are numeric SAS dates and times:

Last_name        First_name      ID        DOB_date     DOB_time     Service_start_date      service_start_time

Prinze Jr            Freddie            124A   12/21/1951   12:00AM        07/31/2014                  00:00:00

Doe                   John                 100B    04/06/1955   12:00AM       05/14/2014                  00:00:00

Abdul Jawad      Joe                  325A    04/21/1941    12:00AM      07/29/2015                  00:00:00

I tried playing around with the substring function but was havign trouble. You'll notice that there is no space after the comman in "last,first" but there is one in Joe's after the comma:"Abdul Jawad, Joe" and there is two spaces after April since the date, 6, only is one digit, but for everything else there is one space between the month and the day in DOB. The times are all the same, but it would be nice to have them in case if someone's isn't 12:00AM or 00:00:00. I couldn't find the format for "Month day year" where month is abreviated and year is 4 digits (like what I have). I have been reading through the other postings to see if I could find a solution but alas, have not been able to solve this. Any help would be appreciated 🙂

Thanks,

Gina

[ballardw]

So we don't make a lot of assumptions and /or have to retype data please look here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... for instructions on how to turn your existing SAS data set into data set code that be pasted here or attached as a text file. Then we have something we can test on.

Note: single field Names are one of the worst data contents to parse out, only exceeded by single field addresses in common data.

LastName =scan(MemberNameNum,1,',');

likely works for most. We may have to get more creative with the rest.

Please look at your data and see if the TIME part is ever other than 12:00AM for DOB or 00:00:00 for ServiceStart. If not then you do not actually have any time values to consider but are likely the victime of a lazy database designer that allows DATE values to ge stored as DATETIME without time values. Then the data looks like it actually has times but doesn't.

The DOB may yield to:

DobDate =  input(  cats(scan(dob,2),scan(dob,1),scan(dob,3), date9.);

format dobdate mmddyy10.

Service_start_date = input(scan(servicestart,1,' '),yymmddD.);

format Service_start_date mmddyy10.;

[Ksharp]

If you have SAS9.4, try anydtdtm.

data have;
input MemberNameNum     & $40.          DOB   & $40.  ServiceStart & $40.;
_dob=input(DOB,anydtdtm32.);
_start=input(ServiceStart,anydtdtm32.);
format _dob _start datetime.;
cards;
Prinze Jr,Freddie(124A)       Dec 21 1951 12:00AM          2014-07-31 00:00:00
Doe,John(100B)                   Apr 6 1955 12:00AM            2014-05-14 00:00:00
Abdul Jawad, Joe(325A)      Apr 21 1941 12:00AM          2015-07-29 00:00:00
;
run;
 proc print;run;