BookmarkSubscribeRSS Feed
india2016
Pyrite | Level 9

I have a dataset with time and survey_id as given below:
Time                            Survey_ID
07-06-2017 12:10        1234
16-06-2017 11:10         5678

I want to create a dataset wherein I want to select the survey_id and time WRT latest time

I have tried this code
proc sql;
create table login2 as select Survey_ID, max(Time_Stamp) format=datetime20. from work.login ;
quit;

but I am getting

Time                          Survey_ID
16-06-2017 11:10      1234
16-06-2017 11:10       5678

 

I want output as shown below:

Time                             Survey_ID

16-06-2017 11:10         5678

 

 

2 REPLIES 2
ChrisBrooks
Ammonite | Level 13

Try changing your SQL to include a Having Statement e.g. (and untested)

 

proc sql;
	create table login2
        as select survey_id, time_stamp format=datetime20.
	from work.login
	having time_stamp=max(timestamp);
quit;

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26
proc sort data=have;
  by descending time_stamp;
run;
data want;
  set have;
  if _n_=1;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 662 views
  • 0 likes
  • 3 in conversation