Split a date/time string

[lovedieer]

HI, All:

I have a SAS data where a date column is a mixture of Date and date/time, and the column is a string. the column looks like below:

1/4/2017

1/31/2017

1/10/20176:18:22AM

1/10/201712:18:28AM

1/3/201711:08:58AM

1/3/201711:9:1AM

1/30/20177:53:21AM

I want to seperate only the mm/dd/yy information from the string. All the year are 2017, is that possible to use 2017 as a delimiter to get MM/DD/YYYY from the strings? How to do it?

[Shmuel · Accepted solution]

You can do it by:

data want;
    infile cards;
    input dtime $;

   date_part = substr(dtime,1,index(dtime,'2017')+3); 
   date_in = input(date_part, ddmmyy10.);

cards;
1/4/2017
1/31/2017
1/10/20176:18:22AM
1/10/201712:18:28AM
1/3/201711:08:58AM
1/3/201711:9:1AM
1/30/20177:53:21AM
; run;

[lovedieer]

Thank you very much. Finally i can get the date out.

[art297]

Here is one way:

data want;
  input @;
  sub=index(_infile_,'2017')+3;
  _infile_=substr(_infile_,1,sub);
  input date : mmddyy10.;
  format date date9.;
  cards;
1/4/2017
1/31/2017
1/10/20176:18:22AM
1/10/201712:18:28AM
1/3/201711:08:58AM
1/3/201711:9:1AM
1/30/20177:53:21AM
;

Art, CEO, AnalystFinder.com

[lovedieer]

thank you. it help me to pull the date out from the long string.

[collinelliot]

There's probably many ways to do this, but this is what I'd to to get the date and time extracted. 

data dates;
format date mmddyy. time timeampm.;
input @1 dateTxt $32.;
dateTxt = prxchange('s/2017/2017~/', 1, dateTxt);
date = input(scan(dateTxt, 1, '~'), mmddyy10.);
time = input(scan(dateTxt, 2, '~'), time10.);
datalines;
1/4/2017
1/31/2017
1/10/20176:18:22AM
1/10/201712:18:28AM
1/3/201711:08:58AM
1/3/201711:9:1AM
1/30/20177:53:21AM
;

[Jagadishkatam]

alternatively with call prxsubstr

data dates;
format date mmddyy. time timeampm.;
input @1 dateTxt $32.;
prxid=prxparse('/\d{1,2}\/\d{1,2}\/\d{2,4}/');
call prxsubstr(prxid,datetxt,start,length);
date=input(substr(dateTxt,start,length),mmddyy10.);
time=input(substr(dateTxt,length+1),time.);
datalines;
1/4/2017
1/31/2017
1/10/20176:18:22AM
1/10/201712:18:28AM
1/3/201711:08:58AM
1/3/201711:9:1AM
1/30/20177:53:21AM
;