How to get max created date

[Gil_]

I have a table i need to get max date.

Proc sql;
Create table test as
Select
Id.
Balance.
Max(Create ) as Date
Fr;m tedt1
,
Run;

It brings everything. The create is a datetime20 format. The output is converted to 1859756644

[novinosrin]

Can you plz post a sample of what you HAVE(Data) and what you want(Data) as output?

[ChrisHemedinger · Accepted solution]

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.

[ballardw]

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.

[ShiroAmada]

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;

[Gil_]

Im still getting all of entries not the maxdate.
Proc sql ;
Create table table as
Select
Id,
Balance,
Created
From table1
Group by dnd,balance,created
Having created =max(created)
;
Run;
Quit;

The created data looks like this
Id. Balance. Created
101. 120. 10DEC2018:19:11:30
101. 500. 10DEC2018:18:11:56

I would want
Id. Balance. Created
101. 120. 10DEC2018:19:11:30

[Gil_]

Got it thanks