11-10-2016 06:12 PM
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
11-10-2016 06:27 PM
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.
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.;
11-11-2016 07:19 AM
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;