What is the earliest date you expect to encounter? If this is financial data, I doubt you need to worry about the 17th century
That is correct. 1975 is about the earliest that we will ever encounter, but because of the logic of the problem, essentially everything after 1913 is what you are going to end up getting, I think.
Thanks for your help!
Solving problems like this is fun!
As @Rick_SAS and others have pointed out very clearly, any kind of "logic" based on the date string in the filename alone will fail as soon as you have two identical date strings with different meanings. However, if you restrict the set of potential dates as far as possible (exclude Sundays, holidays, ... whatever) you may be able to exclude at least some ambiguous cases. The following code could be helpful to get a complete overview of the ambiguous cases. It starts with all possible date strings in the range 01JAN1975 - 31DEC2074. Feel free to adapt this date range as you like and insert selection or exclusion criteria as appropriate.
/* Create all possible date strings in a range */
data alldates;
do d='01JAN1975'd to '31DEC2074'd; /* adapt the date range as you like */
dc=put(d, yymmddn8.); f='1'; output; /* YYYYMMDD */
if day(d)=1 then do;
dc=put(d, yymmn6.); f='2'; output; /* YYYYMM */
end;
dc=put(d, yymmddn6.); f='3'; output; /* YYMMDD */
end;
format d date9.;
run;
/* Select the ambiguous cases, determine max. number of interpretations */
proc sql;
create table ambig0 as
select *, count(*) as c
from alldates
group by dc
having c>1
order by dc, d;
select max(c) into :mc
from ambig0;
quit;
/* Enhance and restructure dataset of ambiguous cases */
data ambig;
do until(last.dc);
set ambig0;
by dc;
array date[&mc];
length fcomb $%eval(2*&mc-1);
fcomb=catx(',',fcomb,f);
i=sum(i,1);
date[i]=d;
end;
drop d f i;
format date: date9.;
run;
As you can see, with the above date range there are 144 ambiguous cases, all involving two possible interpretations, the second and third format and the year 2020. Now you can concentrate on these cases:
@FreelanceReinh, thank you veyr much.
I was just finishing a solution very similar to this. I think that this is one of the best ways to go about it!
Thank you,
John
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.