topic Re: Process textual date data removing invalid parts in SAS Programming

Process textual date data removing invalid parts

ravindra2 — Mon, 22 Oct 2018 09:45:57 GMT

hi i am having the data like this

data date1;
input date1 $ 12.;
cards;
30oct2016
12jun2018
oct2016
2016
unk2016
ununk2017
ukoct2018
uuuu
unun
01unk2020
01jan2017
;

i need report as

30oct2016

12jun2018

oct2016

2016

2017

oct2018

2020

01jan2017

i want like this can any one help me

Re: Process textual date data removing invalid parts

RW9 — Mon, 22 Oct 2018 09:48:04 GMT

You can use the function tranwrd to remove parts from a string:

data want;
  set date1;
  date=tranwrd(date,"unk","");
  date=tranwrd(date,"uuuu","");
  date=tranwrd(date,"uk","");
  date=strip(compress(date));
run;

However I am not sure why you have "." in for missings, this is text we are talking about here, dates would need to have all parts of the date to be convertable to date format.

Re: using functions

Shmuel — Mon, 22 Oct 2018 10:35:54 GMT

Try truncate any leading 'u' or 'n' characters, using a loop until first character not in those letters:

len = length(date);
ch = substr(date,1,1);
do while (ch in ('u', 'n') and len > 0);
   if substr(date,1,3) = 'nov' then leave;
     date = substr(date,2);
     len - 1; 
    ch = substr(date,1,1);
end;

Re: Process textual date data removing invalid parts

s_lassen — Mon, 22 Oct 2018 11:47:36 GMT

I think that what you want is something like this:

data want;
  set date1;
  if length(date1)=9 then
    if input(date1,?? date9.)=. then
	  date1=substr(date1,3);
  if length(date1)=7 then
    if input(date1,?? monyy7.)=. then
	  date1=substr(date1,4);
  if length(date1)=4 then
    if input(date1,?? 4.)=. then
	  date1='.';
run;

Note the lack of ELSE statements: for once, it is intended (e.g. "ununk2018" is first shortened to "unk2018" and then to "2018").