Having clause in PROC SQL question

[KevinC_]

Hello everyone,

I am trying to select letter data where the max letter_date for each customer (each customer has multiple letter_dates) is less than the resp_date.  In other words, I need the correspoinding data for the row that has the max letter_date.  Is my having clause correct?

proc sql;

create table tab1 as

select

a.cust_id,

b.letter_date,

a.resp_date,

a.letter_type

from cust_table  a

left join letter_table  b

on a.cust_id = b.cust_id

group by b.cust_id

having (b.letter_date = max(b.letter_date)) and (b.letter_date < a.resp_date)

order by a.cust_id

;

quit;

Thank you in advance for any input!

[PGStats]

Why a left join?

What do you want to get for cust_ids where max(letter_date) > resp_date ?

What do you want to get when there is no letter_date for a given cust_id ?

[Florent]

Hi,

If I understood it correctly, the following query should work fine (I am not using the HAVING clause though):

proc sql;

      create table tab1 as

      select   a.cust_id,

            b.letter_date,

            a.resp_date

      from cust a

      left join letter b

            on a.cust_id = b.cust_id

      where b.letter_date = (select max(c.letter_date) from letter c where c.cust_id = b.cust_id group by c.cust_id)

        and b.letter_date < a.resp_date

      order by a.cust_id

      ;

quit;

[DBailey]

This is how I would do it also except that I don't think you need the group by in the subquery:

proc sql;

      create table tab1 as

      select   a.cust_id,

            b.letter_date,

            a.resp_date

      from cust a

      left join letter b

            on a.cust_id = b.cust_id

      where b.letter_date = (select max(c.letter_date) from letter c where c.cust_id = a.cust_id and c.letter_date < a.resp_date)

      order by a.cust_id

      ;

quit;

[Ksharp]

It looks like you need a where clause. But yours also looks good.

where (b.letter_date < a.resp_date)

group by b.cust_id

having (b.letter_date = max(b.letter_date))

order by a.cust_id

Ksharp