BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gil_
Quartz | Level 8
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
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20

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

ChrisHemedinger
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ballardw
Super User

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
Lapis Lazuli | Level 10

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_
Quartz | Level 8
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_
Quartz | Level 8
Got it thanks

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 23411 views
  • 2 likes
  • 5 in conversation