How to ID min max

[BETO]

How do I get the min time an max time by number? Data looks like this 

id. Number. Start time. End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:05:11
1a. 24. 01/01/16 11:02:00. 01/01/16 11:06:16
1a. 25. 01/01/16. 12:00:00. 01/0116 12:07:00

I would want want it to look like this outpout

id. Number.                    Min Start time.        Max End time

1a. 23.                    01/01/16. 11:01:00.        01/01/16 11:06:11

thanks 

[Reeza]

Proc Means, Summary, Univariate, SQL, a Data Step

There are a lot of ways to generate descriptive statistics.

https://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#p070bkysj4lkyun0zxj4...

[BETO]

Hi reeza,
Is there a way using proc sql or a data step ?

[JohndeKroon]

What do you want to do here? 1a. 23. seems to be a unique combination, so why assign 01/01/16 11:06:11 to this key as the End time?

what would be your decision rule here?

[BETO]

I have a ID of 1 with a number of 23 ....sorry I just notice the 24 should be a 23 number so since I have 2 entries on same date same id and same number I want to determine the min between those 2 and the max between this 2

[JohndeKroon]

Ah! That makes sense 🙂

Try the following:

proc sql;
	create table work.test as select
		id,
		number,
		min(start_time) format=date9. (or whatever format you like),
		max(end_time) format=date9.(or whatever format you like)
	from <yrtablename>
		group by id, number;
quit;

Hope this helps

[BETO]

I am getting these results
Here is data

id. Number. Start time. End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:05:11
1a. 24. 01/01/16 11:02:00. 01/01/16 11:06:16
1a. 25. 01/01/16. 12:00:00. 01/0116 12:07:00

I'm getting
id. Number. Start time. End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:05:11

when it should be this

id. Number. Start time. End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:06:00
the min part works but max it give me the end time to the min what I'm looking for the max time to the end time almost there thanks

[JohndeKroon]

Shouldn't you change the 24 into 23? That's what I understood from your previous reaction...

You suggested that your data should look like:

id. Number. Start time. End time
1a. 23. 01/01/16. 11:01:00. 01/01/16 11:05:11
1a. 23. 01/01/16 11:02:00. 01/01/16 11:06:16
1a. 25. 01/01/16. 12:00:00. 01/0116 12:07:00

Or did I get you wrong here?

[Reeza]

@BETO Post your code, the code posted references end_time in the max function.

[BETO]

Yes sorry didn't change it should be 23

[JohndeKroon]

Hopefully the code now works for you!?

[BETO]

Here is my code
Proc sql ;
Create table report as
(Select
ID,
NUMBER,
START_TIME,
END_TIME,
MIN (START_TIME) FORMAT=DATE9.,
MAX (END_TIME)FORMAT=DATE9.
FROM REPORT
GROUP by Id, numbe
); run;

The date9. Is coming up 1.76501E9

Yes it's number 23

[Reeza]

You have a datetime variable, not date, so try datetime21. or dtdate9. formats.

[JohndeKroon]

I guess that in total this should do your trick:

option datestyle=mdy;
data have;
input id $ Number  (Starttime Endtime) (: anydtdtm.);
format Starttime Endtime datetime.;
cards;
1a. 23 01/01/16:11:01:00  01/01/16:11:05:11
1a. 23 01/01/16:11:02:00  01/01/16:11:06:16
1a. 25 01/01/16:12:00:00  01/01/16:12:07:00
;
run;

proc sql;
	create table want as select
		id,
		number,
		/*starttime,
		endtime,*/
		min(starttime)  format=datetime.,
		max(endtime) format=datetime.
	from have
		group by id, number;
quit;

Note that you should omit starttime and endtime as direct outputs of your sql-statement. The group by won't work if you do, because your rows are not uniquely identified by only id and number. 

Hopefully this is what you want.

[BETO]

Thanks reeza. I'm still getting the min and max from same entries it doesn't look at both entries any suggestions?