CODEWITHSUNDEEP
sasenterprise-guide
HermannHH
HermannHH

Reputation: 1772

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?

Upvotes: 0

Views: 442

Answers (2)

Jeff
Jeff

Reputation: 1807

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().

Upvotes: 3

Longfish
Longfish

Reputation: 7602

You can certainly simplify the select statement by using a single informat to read in the date, instead of 3 substring calculations. It obviously needs to do this for every record, so it will still slow it down, but hopefully not as much as currently.

proc sql noprint ;
 select MAX(input(mydate,yymmdd10.)) as max_date FORMAT=date9.
 into :max_date
 from FooData;
quit;

Upvotes: 1

Related Questions