topic Proc SQL max, group by and where in SAS Procedures

Proc SQL max, group by and where

apple — Wed, 10 Feb 2016 03:03:00 GMT

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;

Re: Proc SQL max, group by and where

stat_sas — Wed, 10 Feb 2016 03:20:54 GMT



proc sql;
create table filtered4 as
select UIN, max(Date) as MaXDate
from have  
where ID='1'  
group by UIN ;
quit;

Re: Proc SQL max, group by and where

PGStats — Wed, 10 Feb 2016 03:24:34 GMT

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;