I want to find the max Date for each UIN where ID=1
so for UIN=A1234 --> maxDate=20150601
UIN=A1235 --> maxDate=20140101
However, my below code doesn't work. THank you
DATA have;
input UIN $ ID $ Date ;
datalines;
A1234 2 20151001
A1234 1 20150601
A1234 5 20150101
A1234 1 20141001
A1235 2 20141001
A1235 6 20140601
A1235 1 20140101
;
run;
proc sql;
create table filtered4 as
select *, max(Date)
where ID='1' as MaXDate from have
group by UIN ;
quit;
proc sql;
create table filtered4 as
select UIN, max(Date) as MaXDate
from have
where ID='1'
group by UIN ;
quit;
Use a having clause and fix the syntax
proc sql;
create table filtered4 as
select UIN, ID, Date as MaxDate
from have
where ID='1'
group by UIN
having Date = max(Date);
quit;
or put all non-aggregated variables in the group by clause
proc sql;
create table filtered5 as
select UIN, ID, max(Date) as MaxDate
from have
where ID='1'
group by UIN, ID;
quit;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.