topic Re: PRXCHANGE or RXCHANGE for String Dates in SAS Procedures

PRXCHANGE or RXCHANGE for String Dates

Pdogra — Mon, 13 Sep 2010 18:17:06 GMT

Experts:

I am facing a situation

I have date in a format M/D/YYYY and this is a string.
m stands for single digit month (i.e. 1 instead of 01 for Jan).
d stands for single digit data (e.g. 1 instead of 01 for first day of themonth, YYYY - 4 digit year).

Is there a way I can use rxchange or prxchange and look for single digit month & day and convert them to double digit. eg

7/07/2010 to 07/07/2010 or
7/7/2010 to 07/07/2010 or
10/1/2010 to 10/01/2010

Here's what I tried:
DATA try;
INPUT dt $CHAR40.;
txt= prxparse( 's/(\d\/)/0\d\//');
text = prxchange(txt, -1, dt);
DATALINES;
7/07/2009
;

result :
dt txt text

7/07/2009 1 0\d/00\d/2009 Message was edited by: Pdogra

Re: PRXCHANGE or RXCHANGE for String Dates

art297 — Mon, 13 Sep 2010 20:17:30 GMT

If you want to learn regular expressions, by all means, let the forum know that you are still waiting for a response. However, you indicated that you wanted to correct the situation that you had. You can do that with only using put and input. For example:

data want;
set have;
want_date=put(input(have_date,mmddyy10.),mmddyy10.);
run;

HTH,
Art

Re: PRXCHANGE or RXCHANGE for String Dates

Pdogra — Mon, 13 Sep 2010 20:55:24 GMT

wouldn't mmddyy10 informat consistently need 2 digits for month 2 digits for day and 4 for year ?

The strings date I have are erratic mostly 1 digit for months till oct and 2 digits for oct,nov and dec and same is case for day single digit till 9th day of month and 2 digits from 10 till 30/31 day of the month.

Re: PRXCHANGE or RXCHANGE for String Dates

sbb — Mon, 13 Sep 2010 22:51:37 GMT

As demonstrated in the SAS program below, the answer is no.

Scott Barry
SBBWorks, Inc.

5 data _null_;
6 dt = input('1/1/2010',mmddyy10.);
7 put dt= date9. dt= mmddyy10.;
8 run;

dt=01JAN2010 dt=01/01/2010
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 second

Re: PRXCHANGE or RXCHANGE for String Dates

art297 — Tue, 14 Sep 2010 00:04:05 GMT

I agree with Scott. Try the following:

data have;
input have_date $10.;
format date date9.;
date=input(have_date,mmddyy10.);
cards;
7/1/2010
06/5/2009
04/05/2010
5/06/2010
;

Art

Re: PRXCHANGE or RXCHANGE for String Dates

deleted_user — Tue, 14 Sep 2010 10:04:23 GMT

If not the most appropiate, the perl parsing solution is interesting.

DATA try;
INPUT dt $CHAR40. ;
length text $10. ;
retain txt;
if _n_ eq 1 then txt= prxparse( 's/(?<!\d)(\d)\//0$1\//');
text = prxchange(txt, -1, dt);
DATALINES;
6/07/2009
6/7/2010
12/8/2008
;

/(?<!\d)(\d)/ matches any occurrence of digit that does not follow a digit.

/(?<!\d)(\d)\// matches any occurrence of digit that does not follow a digit and that is
followed by a slash. Message was edited by: sensas

Re: PRXCHANGE or RXCHANGE for String Dates

Pdogra — Tue, 14 Sep 2010 12:38:07 GMT

My apologies. Don't mean to make this complex. How about if you have datetime. (I am using SAS 9.1.3)

data have;
input have_date $18.;
format date date9.;
date=input(have_date,datetime16.);
cards;
7/1/2010:12:57
06/5/2009:10:57
04/05/2010:9:48
5/06/2010:6:48
;

or

data have;
input have_date $18.;
format date date9.;
date=input(have_date,datetime16.);
cards;
7/1/2010 12:57
06/5/2009 10:57
04/05/2010 9:48
5/06/2010 6:48
;

Datetime is not being read properly
Result (from first prog)

Obs have_date date

1 7/1/2010:12:57 .
2 06/5/2009:10:57 .
3 04/05/2010:9:48 .
4 5/06/2010:6:48 .

According to SAS : The datetime values must be in the following form: ddmmmyy or ddmmmyyyy, followed by a blank or special character, followed by hh:mm:ss.ss (the time). In the date

Message was edited by: Pdogra Message was edited by: Pdogra

Re: PRXCHANGE or RXCHANGE for String Dates

Pdogra — Tue, 14 Sep 2010 12:44:55 GMT

Hi Sensas,
Thanks the code works. There is bit of complexity. I have time peice attached to date as well. what peice shall I change in the code to make it work.

Data looks like:
6/7/2010 12:56:08.56
10/5/2010 5:46
10/5/2010 15:46:09

I am not an expert, but looks like I may have to write a line to handle digits before colon :

DATA try;
INPUT dt $CHAR40. ;
length text $10. ;
retain txt;
if _n_ eq 1 then txt= prxparse( 's/(?

Re: PRXCHANGE or RXCHANGE for String Dates

deleted_user — Tue, 14 Sep 2010 13:05:59 GMT

The code works with or without time pieces because the reg expression just looks for alone digit followed by a slash.

You just have to change the text variable length :

DATA try;
INPUT dt $CHAR40. ;
length text $40. ;
retain txt;
if _n_ eq 1 then txt= prxparse( 's/(?<!\d)(\d)\//0$1\//');
text = prxchange(txt, -1, dt);
DATALINES;
6/7/2010 12:56:08.56
10/5/2010 5:46
10/5/2010 15:46:09
;

Re: PRXCHANGE or RXCHANGE for String Dates

deleted_user — Tue, 14 Sep 2010 13:18:50 GMT

With a few changes to the reg expression you can also change time pieces :

DATA try;
INPUT dt $CHAR40. ;
length text $40. ;
retain txt;
if _n_ eq 1 then txt= prxparse( 's/(?<!\d)(\d(?:\/|:))/0$1/');
text = prxchange(txt, -1, dt);
DATALINES;
6/7/2010 12:56:08.56
10/5/2010 5:46
10/5/2010 15:46:09
;

5:46 will be changed into 05:46

A perl regexp documentation link : http://perldoc.perl.org/perlre.html
Be carefull: SAS doesn't implement perl syntaxe in its whole...

Re: PRXCHANGE or RXCHANGE for String Dates

Pdogra — Tue, 14 Sep 2010 14:28:57 GMT

Thank you very much Sensas.

Could you please piont me to documentation to help augment my knowledge with Regex ?

Also what would be significance of ! and | in your code.
if _n_ eq 1 then txt= prxparse( 's/(?

Re: PRXCHANGE or RXCHANGE for String Dates

art297 — Tue, 14 Sep 2010 14:41:25 GMT

Two last points regarding a non-PERL solution, but ones which you will need to take into account with either approach: (1) you can't format a datetime with a date format and (2) if you ultimately want to input the datetimes as SAS datetime formatted variables, they will have to be structured in a way that one of the informats can be used.

The following, I think, does that for the sample data you provided:

options datestyle=mdy;

data have;
input have_date $18.;
format date date9.;
date=datepart(input(have_date||":00:00",anydtdtm20.));
cards;
7/1/2010:12:57
06/5/2009:10:57
04/05/2010:9:48
5/06/2010:6:48
;

Art

Re: PRXCHANGE or RXCHANGE for String Dates

sbb — Tue, 14 Sep 2010 15:22:23 GMT

Not sure the relevance, but a DATETIME variable (seconds since 1/1/1960) can be formatted to only display the date-portion, as shown below:

9 data _null_;
10 x = datetime();
11 put x= datetime7. x=datetime9.;
12 run;

x=14SEP10 x=14SEP2010
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 second

The important point to emphasize here is that you have limited sorting/display control over a character-formatted DATE and/or DATETIME character-string; also consider that it may not even be legitimate - yet another reason to run it through a SAS-standard INFORMAT rather than a band-aid approach using PERL, which frankly makes the eyes on my mouse-cursor roll back when I see the command/function syntax. The best approach here is to use the INPUT function with an appropriate SAS INFORMAT, and let SAS decide if the character-string is or is not a validate numeric DATE or DATETIME value.

Scott Barry
SBBWorks, Inc.

Re: PRXCHANGE or RXCHANGE for String Dates

deleted_user — Tue, 14 Sep 2010 15:35:10 GMT

(?<!pattern)

A zero-width negative look-behind assertion. For example /(?<!bar)foo/ matches any occurrence of "foo" that does not follow "bar". Works only for fixed-width look-behind.

Source: perl documentation

The "!" means "not", "<" means "back" and ? means "is ?".

So you can translate (?<!pattern) by "Is the pattern not preceding ?" If it's true then you are sure "pattern" is not preceding what you're looking for.

"|" is the or operator.
In that expression : "\d(?:\/|:)", i search for a digit that would be followed imediately by \ OR :.

You can search for SAS documentation on perl expression by googleying "site:sas.com SUGI Perl"

Re: PRXCHANGE or RXCHANGE for String Dates

Pdogra — Tue, 14 Sep 2010 19:16:49 GMT

Appreciate your help sensas.

Thanks,
PD

Re: PRXCHANGE or RXCHANGE for String Dates

Pdogra — Wed, 15 Sep 2010 14:34:26 GMT

Hi Sensas,

I played with regex little bit, but I guess I am no way closer to your level of expertise.

I am trying 2 things
1) Look for milliseconds from the time part and remove it e.g.
12/9/2010 18:06:54.55 (remove .55)
2) Look for seconds if not found then append :00 (e.g. 12/9/2010 18:064 to 12/9/2010 18:06:00 )

Is it possible to do in the same line ? Sorry don't mean to make you do my job. I am sure by the time I wil figure this out , you would have a solution for it..

Thanks,

Here's your code:

DATA try;
INPUT dt $CHAR40. ;
length text $40. ;
retain txt;
if _n_ eq 1 then txt= prxparse( 's/(?

Re: PRXCHANGE or RXCHANGE for String Dates

sbb — Wed, 15 Sep 2010 15:35:19 GMT

Hey Fellow SAS_Forum_Campers,

Is this a SAS forum or a Perl forum?

After converting a character-string to a SAS DATETIME variable (seconds since 1/1/1960), you can use the SAS DATETIME format or you can extract the time-portion using the SAS CALL function TIMEPART. See below.

Scott Barry
SBBWorks, Inc.

22 data _null_;
23 dt = input('1/31/2009',mmddyy10.);
24 dttm = dhms(dt,9,9,9.222);
25 tm = timepart(dttm);
26 put dt= date9. dttm= datetime7. dttm=datetime9. tm= time12.3;
27 run;

dt=31JAN2009 dttm=31JAN09 dttm=31JAN2009 tm=9:09:09.222
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 second

Suggested Google advanced search arguments, this topic / post:

using sas dates site:sas.com

datetime date introduction site:sas.com

Re: PRXCHANGE or RXCHANGE for String Dates

Pdogra — Wed, 15 Sep 2010 18:08:19 GMT

Thanks Sbb.

Well I could also do a substr to carry out the tasks but I am trying to utilise the power of regular expressions and that's very reason why SAS has included that.

Thanks,

PD

Re: PRXCHANGE or RXCHANGE for String Dates

sbb — Wed, 15 Sep 2010 18:34:02 GMT

Okay, so after you have corrected the format, then what? How will you know that the date, for example, 02/29/2010, is or is not valid? Then, what will you do with the string, if sorting "by chronological date" is needed? This is my point.

Scott Barry
SBBWorks, Inc.

13 data _null_;
14 dt = input('02/29/2010',mmddyy10.);
15 put dt= date.;
16 run;

NOTE: Invalid argument to function INPUT at line 14 column 6.
dt=.
dt=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of
the operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 14:6
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds