BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mishka1
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Peter_C
Rhodochrosite | Level 12

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

View solution in original post

18 REPLIES 18
art297
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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
Fluorite | Level 6

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

FriedEgg
SAS Employee

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
Rhodochrosite | Level 12

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
Fluorite | Level 6

Works right out of the box. Thanks!

art297
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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

Mishka1
Fluorite | Level 6

Ah ha! I got it.

I had to increase the substring constant.

substr(FDWCQueues,x+ 11

Ksharp
Super User
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
Rhodochrosite | Level 12

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
Super User

You are welcome.  Peter.

Merry Christmas!

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 1431 views
  • 6 likes
  • 6 in conversation