topic Re: How to get max created date in SAS Procedures

How to get max created date

Gil_ — Fri, 07 Dec 2018 19:40:46 GMT

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

Re: How to get max created date

novinosrin — Fri, 07 Dec 2018 19:43:49 GMT

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

Re: How to get max created date

ChrisHemedinger — Fri, 07 Dec 2018 19:57:43 GMT

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.

Re: How to get max created date

ballardw — Fri, 07 Dec 2018 20:46:42 GMT

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.

Re: How to get max created date

ShiroAmada — Mon, 10 Dec 2018 09:41:43 GMT

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;

Re: How to get max created date

Gil_ — Tue, 11 Dec 2018 18:37:10 GMT

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

Re: How to get max created date

Gil_ — Tue, 11 Dec 2018 18:46:22 GMT

Got it thanks