Help using Base SAS procedures

Proc SQL max, group by and where

Reply
Frequent Contributor
Posts: 75

Proc SQL max, group by and where

 

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;
Trusted Advisor
Posts: 1,204

Re: Proc SQL max, group by and where

[ Edited ]


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

 

Respected Advisor
Posts: 4,651

Re: Proc SQL max, group by and where

 

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
Ask a Question
Discussion stats
  • 2 replies
  • 549 views
  • 0 likes
  • 3 in conversation