topic Re: Having clause in PROC SQL question in SAS Procedures

Having clause in PROC SQL question

KevinC_ — Fri, 26 Apr 2013 14:55:12 GMT

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!

Re: Having clause in PROC SQL question

PGStats — Fri, 26 Apr 2013 15:17:41 GMT

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 ?

Re: Having clause in PROC SQL question

Florent — Mon, 29 Apr 2013 12:55:40 GMT

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;

Re: Having clause in PROC SQL question

Ksharp — Mon, 29 Apr 2013 13:20:43 GMT

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

Re: Having clause in PROC SQL question

DBailey — Mon, 29 Apr 2013 13:32:31 GMT

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;