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!
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
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;
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'
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;
For only just learning regular expressions, you sure do a good job of making stuff that works. Thanks!
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.
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
Works right out of the box. Thanks!
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.
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?
Yes, there was an error in my original code. I just changed it and the extra quote is now removed.
Ah ha! I got it.
I had to increase the substring constant.
substr(FDWCQueues,x+ 11
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
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
You are welcome. Peter.
Merry Christmas!
Ksharp
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.