I’m trying to change the funky date format coming in to a SAS date expression. '02FEB2012'd for example.
I am importing a string that may have one or more dates that look like this: 222201200:00:0DATE or 1222201200:00:0DATE in mmddYYYY00:00:0DATE format. The underlined part will always be the same (00:00:0DATE). The month and day are either single or double digits.
I’m trying the below but my skills lack my needs. Thanks for your help:
Data input;
input str $ 1-92 want $ 93-174 ;
cards;
This is string 1 where date eq. 11201200:00:0DATE This is string 1 where date eq. '01JAN2012'd
This is string 2 where dates between 22201200:00:0DATE and 121201200:00:0DATE end of strings This is string 2 where dates between '02FEB2012'd and '01DEC2012'd end of strings
;
run;
data want;
set input;
pnum=prxparse("/\d\d\d\d\d\d\d\d\d\:\d\d\:\d\w/o");
x=1;
do while (x gt 0);
x = prxmatch(pnum,str);
if x then do;
str1=catt(substr(str,1,x-1),
put(input(substr(tranwrd(str,"00:00:0DATE",""),x,8), anydtdte12.),date9.),"'d",
substr(str,x+9));
end;
end;
run;
ptions datestyle=mdy;
data foo;
input str $ 1-92 want $ 93-174;
start=1;
stop=length(str);
prx=prxparse('/\d{6,8}00:00:0DATE/');
call prxnext(prx,start,stop,str,pos,len);
do while (pos > 0);
substr(str,pos,len)=cats("'",put(input(prxchange('s/(\d{1,2})(\d{1,2})(\d{4})00:00:0DATE/$1-$2-$3/',1,substr(str,pos,len)),anydtdte.),date9.),"'d");
call prxnext(prx,start,stop,str,pos,len);
end;
str=compbl(str);
valid=(str=want);
keep str want valid;
cards;
This is string 1 where date eq. 11201200:00:0DATE This is string 1 where date eq. '01JAN2012'd
This is string 2 where dates between 22201200:00:0DATE and 121201200:00:0DATE end of strings This is string 2 where dates between '02FEB2012'd and '01DEC2012'd end of strings
;
run;
str | want | valid |
This is string 1 where date eq. '01JAN2012'd | This is string 1 where date eq. '01JAN2012'd | 1 |
This is string 2 where dates between '02FEB2012'd and '01DEC2012'd end of strings | This is string 2 where dates between '02FEB2012'd and '01DEC2012'd end of strings | 1 |
ptions datestyle=mdy;
data foo;
input str $ 1-92 want $ 93-174;
start=1;
stop=length(str);
prx=prxparse('/\d{6,8}00:00:0DATE/');
call prxnext(prx,start,stop,str,pos,len);
do while (pos > 0);
substr(str,pos,len)=cats("'",put(input(prxchange('s/(\d{1,2})(\d{1,2})(\d{4})00:00:0DATE/$1-$2-$3/',1,substr(str,pos,len)),anydtdte.),date9.),"'d");
call prxnext(prx,start,stop,str,pos,len);
end;
str=compbl(str);
valid=(str=want);
keep str want valid;
cards;
This is string 1 where date eq. 11201200:00:0DATE This is string 1 where date eq. '01JAN2012'd
This is string 2 where dates between 22201200:00:0DATE and 121201200:00:0DATE end of strings This is string 2 where dates between '02FEB2012'd and '01DEC2012'd end of strings
;
run;
str | want | valid |
This is string 1 where date eq. '01JAN2012'd | This is string 1 where date eq. '01JAN2012'd | 1 |
This is string 2 where dates between '02FEB2012'd and '01DEC2012'd end of strings | This is string 2 where dates between '02FEB2012'd and '01DEC2012'd end of strings | 1 |
Masterful! Thank you.
What does the first "call prxnext(prx,start,stop,str,pos,len);" do? I don't see where pos is defined so I'm not sure how that would work. Is it null?
call prxnext(prx,start,stop,str,pos,len);
pos and len are defined in this subroutine call.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.