CODEWITHSUNDEEP
sqlsasproc-sql
Kumachorou
Kumachorou

Reputation: 37

How do I limit the number of rows in a proc sql table to ones which contain the highest date

I am trying to perform a large query on my mainframe, what I need to do is pull down all of the rows which have the highest PD_START_DATE. I figure it's something like

PROC SQL ;
SELECT PD_PROP_NUM, PD_RATE_PGM, PD_START_DATE, PD_END_DATE, PD_DOW_FREQ, PD_ROOM_POOL, PD_QUOTE_SERIES, PD_RPGM_SEQ_NUM, PD_PROD_LINE 
FROM Sbtddraf.nycomnidat
(SELECTorder by PD_START_DATE 
DESC 
NOBS =PD_START_DATE(MAX)

but I know that doesn't work; Advice appreciated

Upvotes: 0

Views: 838

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

If you are using pass-through SQL and your database supports window functions (which it probably does), then you can do:

PROC SQL ;
    SELECT PD_PROP_NUM, PD_RATE_PGM, PD_START_DATE, PD_END_DATE, PD_DOW_FREQ, PD_ROOM_POOL, PD_QUOTE_SERIES, PD_RPGM_SEQ_NUM, PD_PROD_LINE 
    FROM (SELECT n.*,
                 ROW_NUMBER() OVER (PARTITION BY PD_PROP_NUM ORDER BY PD_START_DATE DESC) as seqnum
          FROM Sbtddraf.nycomnidat n
         ) n
    WHERE seqnum = 1;

There are other ways to express this logic, if this doesn't work.

EDIT:

Here is an alternative:

PROC SQL ;
    SELECT PD_PROP_NUM, PD_RATE_PGM, PD_START_DATE, PD_END_DATE, PD_DOW_FREQ, PD_ROOM_POOL, PD_QUOTE_SERIES, PD_RPGM_SEQ_NUM, PD_PROD_LINE 
    FROM Sbtddraf.nycomnidat n
    WHERE n.PD_START_DATE = (SELECT MAX(n2.PD_START_DATE)
                             FROM Sbtddraf.nycomnidat n2
                             WHERE n2.PD_PROP_NUM = n.PD_PROP_NUM
                            );

Note the WHERE clause. This is saying that you want the most recent record for each PD_PROP_NUM.

Upvotes: 1

Related Questions