topic Re: How to ID min max in SAS Procedures

How to ID min max

BETO — Mon, 18 Jan 2016 18:46:29 GMT

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

Re: How to ID min max

Reeza — Mon, 18 Jan 2016 19:13:31 GMT

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#p070bkysj4lkyun0zxj45n643q1i.htm

Re: How to ID min max

BETO — Mon, 18 Jan 2016 22:05:03 GMT

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

Re: How to ID min max

JohndeKroon — Mon, 18 Jan 2016 22:10:27 GMT

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?

Re: How to ID min max

BETO — Mon, 18 Jan 2016 22:20:10 GMT

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

Re: How to ID min max

JohndeKroon — Mon, 18 Jan 2016 22:27:51 GMT

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

Re: How to ID min max

BETO — Mon, 18 Jan 2016 22:58:25 GMT

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

Re: How to ID min max

JohndeKroon — Mon, 18 Jan 2016 23:01:15 GMT

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?

Re: How to ID min max

Reeza — Mon, 18 Jan 2016 23:02:42 GMT

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

Re: How to ID min max

BETO — Mon, 18 Jan 2016 23:13:12 GMT

Yes sorry didn't change it should be 23

Re: How to ID min max

JohndeKroon — Mon, 18 Jan 2016 23:14:58 GMT

Hopefully the code now works for you!?

Re: How to ID min max

BETO — Mon, 18 Jan 2016 23:21:39 GMT

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

Re: How to ID min max

Reeza — Mon, 18 Jan 2016 23:40:54 GMT

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

Re: How to ID min max

BETO — Tue, 19 Jan 2016 00:04:18 GMT

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

Re: How to ID min max

Reeza — Tue, 19 Jan 2016 01:11:07 GMT

Your GROUP BY tells SAS at what levels to calculate. From your code snippet it looks like you've included Number in your GROUP BY when the numbers are different across the observations. SAS is correctly calculating what you've requested. Remove Numbers from the GROUP BY and Select statement. If you want to include it in the SELECT you'll need some sort of rule, ie max/min of which one to take. If it's first or last then then you'll need to go back to data step logic.

Re: How to ID min max

Ksharp — Tue, 19 Jan 2016 01:40:01 GMT

Here could give you a start :

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. 24 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;
data temp;
 set have;
 if lag(Endtime) lt Starttime or id ne lag(id) then g+1;
run;
data want;
 set temp;
 by g;
 retain start num;
 if first.g then do;start=Starttime;num=number;end;
 if last.g then do;end=Endtime;output;end;
 format start end datetime.;
 drop Starttime Endtime number;
run;

Re: How to ID min max

JohndeKroon — Tue, 19 Jan 2016 06:58:48 GMT

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.

Re: How to ID min max

BETO — Wed, 20 Jan 2016 06:16:05 GMT

Hi thank you everyone e that is assisting me ...whst I have is a data set that looks like this
SITE. NUMBER. START TIME. END TIME
123. A3 06dec2015:8:24:00 .06dec2015:08:26;00

123. A3 06dec2015:8:26:00 .06dec2015:08:40:00

What I want is the min an max of those 2 entries nothing else something like this

123. A3 06dec2015:8:24:00 .06dec2015:08:40:00
The other 2 can be removed
What I'm getting is all 4 entries or just the min
123. A3 06dec2015:8:24:00 .06dec2015:08:26;00
Thank you again

BTW there are other dates in my data sometimes it brings those dates as my min an max for example
123. A3 03dec2015:8:25500 .06dec2015:08:26;00. Which means it's looking st who data I need it to look at each day spefic

Re: How to ID min max

Ksharp — Wed, 20 Jan 2016 08:53:23 GMT

You get what you want based this dataset .

CODE NOT TESTED

proc sort data=have ; by SITE  END_TIME ;run;
data want;
 set have;
 by SITE  ;
 if first.SITE  ;
run;

Re: How to ID min max

BETO — Wed, 20 Jan 2016 16:32:13 GMT

Hi ksharp I added the data step ...where you state data want is it a table your refer or column? I refer to table it failed ? The set an if are highlighted in red log states. 180-322 statement is not valid or it used out of proper order