BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

21 REPLIES 21
Reeza
Super User

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
Fluorite | Level 6
Hi reeza,
Is there a way using proc sql or a data step ?
JohndeKroon
Obsidian | Level 7

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
Fluorite | Level 6
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
Obsidian | Level 7

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
Fluorite | Level 6
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
Obsidian | Level 7

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
Super User

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

BETO
Fluorite | Level 6
Yes sorry didn't change it should be 23
JohndeKroon
Obsidian | Level 7

Hopefully the code now works for you!?

BETO
Fluorite | Level 6
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
Super User

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

JohndeKroon
Obsidian | Level 7

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
Fluorite | Level 6
Thanks reeza. I'm still getting the min and max from same entries it doesn't look at both entries any suggestions?

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 21 replies
  • 2320 views
  • 2 likes
  • 4 in conversation