- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 02-09-2016 10:03 PM
(23898 views)
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;
2 REPLIES 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sql;
create table filtered4 as
select UIN, max(Date) as MaXDate
from have where ID='1'
group by UIN ;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
PG