Split a date/time string

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Split a date/time string

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?


Accepted Solutions
Solution
‎06-05-2017 03:42 PM
Trusted Advisor
Posts: 1,372

Re: Split a date/time string

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;

 

 

View solution in original post


All Replies
Solution
‎06-05-2017 03:42 PM
Trusted Advisor
Posts: 1,372

Re: Split a date/time string

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;

 

 

Occasional Contributor
Posts: 16

Re: Split a date/time string

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

PROC Star
Posts: 7,363

Re: Split a date/time string

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

Occasional Contributor
Posts: 16

Re: Split a date/time string

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

PROC Star
Posts: 288

Re: Split a date/time string

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
;
Trusted Advisor
Posts: 1,128

Re: Split a date/time string

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

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

Discussion stats
  • 6 replies
  • 176 views
  • 1 like
  • 5 in conversation