0

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Kumachorou
  • 37
  • 3

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks so much, I tried that but got an error on the "over" statement. Any other logic you had would be tremendously appreciated. – Kumachorou Mar 15 '16 at 00:54
  • You are a god, like seriously,. I have been hitting this for way more hours than I care to count. **THANK YOU SO MUCH.** now I just have a little clean-up to do. But seriously dude, thank you so much.Also, I'm going to take this apart so I understand exactly what's going on. But really, I can barely say how much I appreciate it. – Kumachorou Mar 15 '16 at 00:59