topic Re: Extracting Date and Time from a Character String when there are spaces in SAS Data Management

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

ginak — Thu, 10 Nov 2016 23:12:52 GMT

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

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

ballardw — Thu, 10 Nov 2016 23:27:01 GMT

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-data-AKA-generate/ta-p/258712 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.;

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

Ksharp — Fri, 11 Nov 2016 12:19:29 GMT

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;