topic Convert dates in a text field in SAS Programming

Convert dates in a text field

Mishka1 — Fri, 16 Dec 2011 14:18:58 GMT

I am starting with a variable that has fully formed SQL logic in it.

For example, Select * From Trans where Trans_date >= '2011-01-01'

The Trans table is now a local SAS data set, so to run the SQL stored in the variable, I need to change the date format from 'YYYY-MM-DD' to 'd-mmm-yy'd

I can do this if I create a lookup table for each date value and use tranwrd to replace them all but it seems to me there would be a more elegant way of doing this.

Since the dates are stored in the text field with the rest of the logic, perhaps trnwrd is the best way to go. Can I get your thoughts/solutions on how best to accomplish this?

Thanks!

Convert dates in a text field

art297 — Fri, 16 Dec 2011 14:33:27 GMT

If all of your statements are like the one shown in your example, you might be able to use something like:

data have;

informat current $60.;

input current &;

cards;

Select * From Trans where Trans_date >= '2011-01-01'

Select * From Trans where Trans_date >= '2011-02-02'

;

data want (drop=x);

set have;

x=index(current,"'");

need=catt(substr(current,1,x-1),"'",

put(input(compress(substr(current,x),"'"),

anydtdte12.),date9.),

"'d");

run;

Convert dates in a text field

Mishka1 — Fri, 16 Dec 2011 15:09:10 GMT

I like that! I do have some competing fields for that index though.

Here is a more real world example. I should have known enough to include it the first time, sorry:

Select * From Trans where

Field1 <> 'D1' AND

Field2 <> 13 AND Field2 <> 61 AND Field2 <> 86 AND

Field3 <> '02' AND Field3 <> '16' AND Field3 <> '86' AND

Tran_dt > '2011-03-26' AND Tran_dt < '2011-04-23'

Re: Convert dates in a text field

art297 — Fri, 16 Dec 2011 16:56:25 GMT

I am only learning regular expressions, thus I'm sure that a more elegant way of writing the following exists. However, it does work for your examples:

/*The following file contains all of your examples*/

data have;

informat current $250.;

infile "c:\trans.txt" lrecl=260;

input current &;

run;

options datestyle=ymd;

data want (drop=x _:);

set have;

_dt_pattern_num=prxparse(

"/\d\d\d\d\-\d\d\-\d\d/o");

x=1;

do while (x gt 0);

x = prxmatch(_dt_pattern_num,current);

/* If found, then parse date */

if x then do;

current=catt(substr(current,1,x-1),

put(input(substr(current,x,10),

anydtdte12.),date9.),"'d",

substr(current,x+11));

end;

run;

Convert dates in a text field

Peter_C — Fri, 16 Dec 2011 17:12:58 GMT

while Art was working on his, I was working on this (someday I'll get into these prx.... but for now,

data have;

input;

line =_infile_;

list; cards4;

Select * From Trans where

Field1 <> 'D1' AND

Field2 <> 13 AND Field2 <> 61 AND Field2 <> 86 AND

Field3 <> '02' AND Field3 <> '16' AND Field3 <> '86' AND

Tran_dt > '2011-03-26' AND Tran_dt < '2011-04-23'

;

Select * From Trans where

Field1 <> 'D1' AND

Tran_dt > '2011-11-01'

;

;;;;

data want;

set have;

date_pos = find( line, 'tran_dt', 'i' );

do while(date_pos);

old1 = scan( substr( line, date_pos), 2, "'" );

open_pos = find( line, "'",date_pos);

clos_pos = find( line, "'",open_pos +2 );

substr( line, open_pos+1,11) = put( input( old1, yymmdd10.), date9.) !! "'d";

date_pos = find( line, 'tran_dt', 'i', clos_pos );

end;

run;

this works by replacing the 'yyyy-mm-dd' with 'ddMMMyyyy'd

Convert dates in a text field

Mishka1 — Fri, 16 Dec 2011 17:51:39 GMT

Works right out of the box. Thanks!

Convert dates in a text field

Mishka1 — Fri, 16 Dec 2011 17:57:13 GMT

For only just learning regular expressions, you sure do a good job of making stuff that works. Thanks!

Convert dates in a text field

art297 — Fri, 16 Dec 2011 18:46:22 GMT

Both Peter's and my solutions should work right out of the box (at least for dates that appear as yyyy-mm-dd, but, realize that the regular expression method would be better if there is more than one date variable. Peter's code would have to be expanded if you had, say, tran_dt and recv_dt or the like.

Convert dates in a text field

Mishka1 — Fri, 16 Dec 2011 19:46:01 GMT

You're right. Having the ability to add new dates without re-coding is the way to go.

When using your solution, it retains the trailing single quote. All the dates are correctly replaced but look like this '25NOV2011'd'

Is there a way to alter your code to remove the single quote after the d?

Convert dates in a text field

FriedEgg — Fri, 16 Dec 2011 20:39:39 GMT

The regular expression can be rewritten as /\d{4}-\d{2}-\d{2}/

Also you can use prxsubstr instead of prxmatch to help with the collection of the data.

Re: Convert dates in a text field

art297 — Fri, 16 Dec 2011 21:04:57 GMT

Yes, there was an error in my original code. I just changed it and the extra quote is now removed.

Convert dates in a text field

Mishka1 — Fri, 16 Dec 2011 21:29:24 GMT

Ah ha! I got it.

I had to increase the substring constant.

substr(FDWCQueues,x+ 11

Re: Convert dates in a text field

Ksharp — Mon, 19 Dec 2011 06:19:31 GMT

data have;
   input;
   line =_infile_;
   list; cards4;
Select * From Trans where
Field1 <> 'D1' AND
Field2 <> 13 AND Field2 <> 61 AND Field2 <> 86 AND
Field3 <> '02' AND Field3 <> '16' AND Field3 <> '86' AND
Tran_dt > '2011-03-26' AND Tran_dt < '2011-04-23'
;
Select * From Trans where
Field1 <> 'D1' AND
Tran_dt > '2011-11-01'
;
;;;;
run;
data want(keep=line);
 set have;
 pid=prxparse("/'\s*\d{4}\s*-\s*\d{1,2}\s*-\s*\d{1,2}\s*'/o");
 start=1;stop=length(line);
 call prxnext(pid,start,stop,line,position,length);
 do while(position > 0);
 date=put(input(dequote(substr(line,position,length)),yymmdd20.),date9.);
 line=substr(line,1,position-1)||quote(date)||'d'||substr(line,position+length);
 call prxnext(pid,start,stop,line,position,length);
 end;
run;

Ksharp

Convert dates in a text field

Peter_C — Mon, 19 Dec 2011 18:48:21 GMT

Ksharp

very good, supporting any single quoted date of form yyyy-mm-dd or yyyy-m-d and with or without a variable name nearby

Convert dates in a text field

Ksharp — Tue, 20 Dec 2011 03:10:22 GMT

You are welcome. Peter.

Merry Christmas!

Ksharp

Convert dates in a text field

art297 — Tue, 20 Dec 2011 05:23:50 GMT

KSharp,

Would you explain your regular expression? Appreciated, Art

Re: Convert dates in a text field

Ksharp — Tue, 20 Dec 2011 06:07:42 GMT

Art

/'\s*\d{4}\s*-\s*\d{1,2}\s*-\s*\d{1,2}\s*'/o

\s* means matching zero or more times SPACE character.

\d{4} means matching exactly four digits.

\d{1,2} means matching one to two times digits(i.e. \d or \d\d)

o means only compiling perl regular expression once.

- means matching exactly - character.

' means matching exactly ' character

Ksharp

Message was edited by: xia keshan

Convert dates in a text field

Patrick — Tue, 20 Dec 2011 10:31:14 GMT

Just for the fun of it:

data want(keep=line);

set have;

line=prxchange("s/'(\d{4})-(\d+)-(\d+)'/%nrbquote(%)sysfunc(inputn($3$2$1,ddmmyy8.))/o", -1, line);

run;

Convert dates in a text field

Ksharp — Wed, 21 Dec 2011 02:55:37 GMT

Patrick.

It is very interesting. I don't realize that changing it into a macro function.

But there is a problem. Your way will make line longer, if the length of original line is not long enough to hold these macro function. then it will be truncated.

Anyway, It is an interesting solution.

Happy Christmas !

Ksharp

Cheers