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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: