Help using Base SAS procedures

How to ID min max

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

How to ID min max

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 


Accepted Solutions
Solution
‎01-20-2016 07:30 PM
Super User
Posts: 10,035

Re: How to ID min max

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;

View solution in original post


All Replies
Super User
Posts: 19,820

Re: How to ID min max

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...

Regular Contributor
Posts: 240

Re: How to ID min max

Hi reeza,
Is there a way using proc sql or a data step ?
Occasional Contributor
Posts: 10

Re: How to ID min max

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?

Regular Contributor
Posts: 240

Re: How to ID min max

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
Occasional Contributor
Posts: 10

Re: How to ID min max

Ah! That makes sense Smiley Happy

 

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

Regular Contributor
Posts: 240

Re: How to ID min max

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


Occasional Contributor
Posts: 10

Re: How to ID min max

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?

Super User
Posts: 19,820

Re: How to ID min max

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

Regular Contributor
Posts: 240

Re: How to ID min max

Yes sorry didn't change it should be 23
Occasional Contributor
Posts: 10

Re: How to ID min max

Hopefully the code now works for you!?

Regular Contributor
Posts: 240

Re: How to ID min max

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






Super User
Posts: 19,820

Re: How to ID min max

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

Occasional Contributor
Posts: 10

Re: How to ID min max

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.

Regular Contributor
Posts: 240

Re: How to ID min max

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 21 replies
  • 598 views
  • 2 likes
  • 4 in conversation