You'll need to add a GROUP BY or HAVING clause depending what you want.
You want the record with the latest date?
select ID, Balance, Max(create) as max format=datetime20.
GROUP BY ID, Balance
HAVING create=MAX(create)
Or the max date for each ID/Balance combination?
select ID, Balance, Max(Create) as max format=datetime20.
GROUP BY ID, Balance
See this post for another example.
Can you plz post a sample of what you HAVE(Data) and what you want(Data) as output?
You'll need to add a GROUP BY or HAVING clause depending what you want.
You want the record with the latest date?
select ID, Balance, Max(create) as max format=datetime20.
GROUP BY ID, Balance
HAVING create=MAX(create)
Or the max date for each ID/Balance combination?
select ID, Balance, Max(Create) as max format=datetime20.
GROUP BY ID, Balance
See this post for another example.
If you want the date portion of a variable containing datetime then the function is DATEPART(variable). You likely want to provide a date format to go along with that.
Perhaps something similar to
Proc sql; Create table test as Select Id, Max(datepart(Create) ) as Date format date9. From tedt1 group by id ; quit;
You code is showing a lot of syntax problems: period instead of comma between select items; a semicolon in the middle of From; an extra comma at the end; and use of RUN instead of QUIT.
but I don't know what value of balance you want.
Try this
data have;
do id=1 to 10;
balance=id*.452;
create_dt=mdy(id,1,2018);
output;
if id>=7 then do;
balance=id*.458;
create_dt=mdy(id,1,2019);
output;
end;
end;
format create_dt date9.;
run;
/* This will yield the same result */
proc sql;
create table want as
select
id,
balance,
max(create_dt) as max_crdt format=date9.
from have
group by id;
quit;
/* I think this is what you want */
proc sql;
create table want as
select
id,
balance,
create_dt
from have
group by id
having max(create_dt)=create_dt;
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: