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!
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 ?
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;
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.