including a max statement inner join with proc sql

Question

I'm trying to get all of all of a series of variables while pulling off of the most recent possible update date (PD_LAST_UPDATE) some fields were updated yesterday, some fields might have been a year ago, so I can't just do PD_LAST_UPDATE = (variable encoded to a specific time) and if I do any set time I'll get way too much data.

Here's my code

(SELECT N1.PD_PROP_NUM, N1.PD_START_DATE, N1.PD_END_DATE, N1.PD_DOW_FREQ, 
        N1.PD_RATE_PGM, N1.PD_ROOM_POOL, N1.PD_QUOTE_SERIES, 
        N1.PD_RPGM_SEQ_NUM, N1.PD_LAST_UPDATE   
        FROM OMP.OMT_PR_SSTRAT_DTL N1

     INNER JOIN OMP.OMT_PROP_SSTRAT AS N2   ON (N1.PD_PROP_NUM=N2.PS_PROP_NUM AND 
                                                N1.PD_START_DATE=N2.PS_START_DATE AND
                                                N1.PD_DOW_FREQ=N2.PS_DOW_FREQ AND
                                                N1.PD_ROOM_POOL=N2.PS_ROOM_POOL)

     WHERE N2.PS_PROP_NUM in (11612) AND **n1.PD_LAST_UPDATE = (MAX)** 
     );

  quit;

The portion of particular interest is bolded, and the prop num ahead of it will be done away with once I can figure out how to select the max value so I can pull down all prob nums. Thanks in advance.

Do you have some records with similar PD_LAST_UPDATE? If yes, how you gonna show the data? If not then it should be no problem. — 4 Leave Cover, May 06 '16 at 01:21
Some of them are the same but some are very different. If I could grab the maximums of all of the PD_LAST_UPDATES it wouldn't be a problem but I have no idea how to do that. — Kumachorou, May 06 '16 at 01:23
I might have a solution but first let me ask a few questions. 1. How many records are you showing? Think of the records you want to display. If it is always 1 record, then you need to max by other fields too. If it will be equal or more than 1 records, then i will provide the solution. — 4 Leave Cover, May 06 '16 at 01:53

Joe · Answer 1 · 2016-05-06T06:47:01.703

You have two ways to filter on the max value of a variable.

One is to group by everything you want to calculate the maximum by, and then use having (which is the after-group-by version of where), like so:

proc sql;
  select origin, make, model
  from sashelp.cars
  group by origin, make
  having mpg_city = max(mpg_city);
quit;

This is allowed in SAS, but not in most other SQL flavors. It's a shortcut to the other method below, largely, and it only works in some particular data structures.

The more traditional approach, then, is to do a correlated subquery:

proc sql;
  select origin, make, model
  from sashelp.cars C
  where mpg_city = (
    select max(mpg_city)
    from sashelp.cars R
    where C.origin=R.origin
      and C.make=R.make
      group by make, origin
    );
quit;

In this case, we're getting to the same place, and more or less getting there the same way - SAS does this on the back end anyway.

In the case of a join, you can either perform this subquery or similar on the dataset prior to the join (or in a subquery whose result is then joined), or you can do so on the result of the join, depending on which is more efficient and whether you need rows from both tables to determine the maximum value.

including a max statement inner join with proc sql

1 Answers1