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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.