Convert dates in a text field

[Mishka1]

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!

[art297]

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;

[Mishka1]

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'

[art297]

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;

  end;

run;

[Mishka1]

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

[FriedEgg]

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.

[Peter_C · Accepted solution]

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

[Mishka1]

Works right out of the box. Thanks!

[art297]

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.

[Mishka1]

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?

[art297]

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

[Mishka1]

Ah ha! I got it.

I had to increase the substring constant.

substr(FDWCQueues,x+ 11

[Ksharp]

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

[Peter_C]

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

[Ksharp]

You are welcome.  Peter.

Merry Christmas!

Ksharp