Search code examples
sasenterprise-guide

What is the quickest way to detect the maximum variable value in SAS

I am trying to find an efficient way of detecting the maximum value of a date variable. One caveat of the data that I will receive daily, is that the date is recorded as text.

I would usually do it in the following way:

proc sql noprint ;
 select MAX(MDY(input(SUBSTRN(MyDate, 6, 2),2.), input(SUBSTRN(MyDate, 9, 2),2.), input(SUBSTRN(MyDate, 1, 4),4.))) as max_date FORMAT=date9.
 into :max_date
 from FooData;
quit;

In this case, the data contains more than 100m rows and running this code every days seems cumbersome. Can anyone think of a more efficient way to approach this issue?

Solution

  • The nice thing about YYYYMMDD is that when you sort it as a string, it also ends up sorted as a date (unlike, for example MM/DD/YY). So just take the max().