PROC SQL & MAX

SvenWagner — Tue, 04 Jun 2019 12:46:54 GMT

Hi,

I came across with a statement like the following:

proc sql;
    create table tmp01 as 
    select    col1
            , col2
            , max(col3, col4) as MaxVar
    from ...
    ;
quit;

So, MAX is used without GROUP BY seemingly as a (row wise) scalar function like within a DATA STEP. Being familiar with "native SQL", I would have expected something like CASE WHEN ELSE END or GREATEST instead of MAX, but the results seem to be the same. Hence, my question is: Can I really use MAX within PROC SQL as it is done within the example? (Correct results also might by coincidence.)

Thanks for your help, Sven

Re: PROC SQL & MAX

Kurt_Bremser — Tue, 04 Jun 2019 12:56:10 GMT

When you use max() (and other such functions, like min() or avg()) with more than one argument, then it works "horizontally" within the current observation.

With only one column name as parameter, it turns into a summary function that works "vertically" for the group (or the whole dataset).

topic PROC SQL & MAX in SAS Procedures

PROC SQL & MAX

Re: PROC SQL & MAX